JavaScript in PostgreSQL pl/v8 – tutorial 2 – operations with tables – plv8.execute

plv8.execute:

• Mostly you will probably use command “plv8.execute”.
• syntax:

  var  = plv8.execute ( 'here your SQL command', [ , ... ] );

• Returns all results as object with two levels of keys:
  • example:

    var result=plv8.execute('select version() as version');

  • Variable result is type “object” – not string as you would probably expect.
  • You can display its structure using this small code:

var result=plv8.execute('select version()');
plv8.elog(NOTICE, "result: ", JSON.stringify(result) );
for (var key in result){
plv8.elog(NOTICE, key);
plv8.elog(NOTICE, result[key]);
plv8.elog(NOTICE, JSON.stringify(result[key]) );
for (var key2 in result[key]){
plv8.elog(NOTICE, key2);
plv8.elog(NOTICE, result[key][key2]);
plv8.elog(NOTICE, JSON.stringify(result[key][key2]) );
}
}

Small note about “plv8.elog(NOTICE, …” – it sometimes happened to me that in one run of function/ do-block I did not get any messages but in next run yes. So far I do not know why.

• You will get output like this:

NOTICE:  result:  [{"version":"PostgreSQL 9.5.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit"}]
NOTICE:  0
NOTICE:  [object Object]
NOTICE:  {"version":"PostgreSQL 9.5.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit"}
NOTICE:  version
NOTICE:  PostgreSQL 9.5.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
NOTICE:  "PostgreSQL 9.5.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit"

• first level are numbers of rows starting with 0, second level are names of columns – therefore it is always necessary to “as <name>” to make sure that names will be as you expect them to be
• so to get result in this example directly:

  var version=result[0]['version'];

• if you have query with more columns then on second level of key you simply use proper column name.
• but if you use PostgreSQL with PL/v8 as back-end and some web application in JavaScript then you can use “JSON.stringify” on whole object and return it as it is

Note about usage of the command “plv8.execute”:

• if you need to use some string in query you have to
  • either combine ” a ‘ like this:

    var result=plv8.execute("select (now() at time zone 'UTC') as nowutc");
    plv8.elog(NOTICE, result[0]['nowutc'])
    

  • or use Escape characters:

    var result=plv8.execute('select (now() at time zone \'UTC\') as nowutc');
    plv8.elog(NOTICE, result[0]['nowutc'])
    

  • or send it as value after query:

    var result=plv8.execute('select (now() at time zone $1) as nowutc', 'UTC');
    plv8.elog(NOTICE, result[0]['nowutc'])
    

  • You cannot embed it into string with double ‘ like in PL/pgSQL: ‘select now() at time zone ”UTC” ‘ – it gives error

    ERROR: SyntaxError: Unexpected string, SQL state: XX000