PLJS/Postgres Integration

PLJS has the ability to execute function calls inside of Postgres.

Scalar Function Calls

In PLJS, you can write your invoked function call in JavaScript, using the usual CREATE FUNCTION statement. Here is an example of a scalar function call:

CREATE FUNCTION pljs_test(keys TEXT[], vals TEXT[]) RETURNS JSONB AS $$
    var o = {};
    for(var i=0; i<keys.length; i++){
        o[keys[i]] = vals[i];
    }
    return o;
$$ LANGUAGE pljs IMMUTABLE STRICT;

=# SELECT pljs_test(ARRAY['name', 'age'], ARRAY['Tom', '29']);

pljs_test
---------------------------
{"name":"Tom","age":"29"}
(1 row)

Internally, the function will defined such as:

(function(keys, vals) {
  var o = {};
  for(var i=0; i<keys.length; i++){
      o[keys[i]] = vals[i];
  }
  return o;
})

Where keys and vals undergo type checking and validation within PostgreSQL, serving as arguments to the function. The object o becomes the result returned as JSONB to Postgres. If argument names are omitted during function creation, they will be available in the function as $1, $2, etc.

Set-returning Function Calls

PLJS supports returning SETOF from function calls:

CREATE TYPE rec AS (i integer, t text);
CREATE FUNCTION set_of_records() RETURNS SETOF rec AS
$$
    // pljs.return_next() stores records in an internal tuplestore,
    // and return all of them at the end of function.
    pljs.return_next( { "i": 1, "t": "a" } );
    pljs.return_next( { "i": 2, "t": "b" } );

    // You can also return records with an array of JSON.
    return [ { "i": 3, "t": "c" }, { "i": 4, "t": "d" } ];
$$
LANGUAGE pljs;

Running this gives you a SETOF result:

=# SELECT * FROM set_of_records();

i | t
---+---
1 | a
2 | b
3 | c
4 | d
(4 rows)

Internally, when the function is declared as RETURNS SETOF, PLJS prepares a tuplestore every time it is called. You can call the pljs.return_next() function as many times as you need to return a row. In addition, you can also return an array to add a set of records.

If the argument object to return_next() has extra properties that are not defined by the argument, return_next() raises an error.

Trigger Function Calls

PLJS supports trigger function calls:

CREATE FUNCTION test_trigger() RETURNS TRIGGER AS
$$
    pljs.elog(NOTICE, "NEW = ", JSON.stringify(NEW));
    pljs.elog(NOTICE, "OLD = ", JSON.stringify(OLD));
    pljs.elog(NOTICE, "TG_OP = ", TG_OP);
    pljs.elog(NOTICE, "TG_ARGV = ", TG_ARGV);
    if (TG_OP == "UPDATE") {
        NEW.i = 102;
        return NEW;
    }
$$
LANGUAGE "pljs";

CREATE TRIGGER test_trigger
    BEFORE INSERT OR UPDATE OR DELETE
    ON test_tbl FOR EACH ROW
    EXECUTE PROCEDURE test_trigger('foo', 'bar');

If the trigger type is an INSERT or UPDATE, you can assign properties of NEW variable to change the actual tuple stored by this operation. A PLJS trigger function will have the following special arguments that contain the trigger state:

  • NEW
  • OLD
  • TG_NAME
  • TG_WHEN
  • TG_LEVEL
  • TG_OP
  • TG_RELID
  • TG_TABLE_NAME
  • TG_TABLE_SCHEMA
  • TG_ARGV

For more information see the trigger section in PostgreSQL manual.

Inline Statement Calls

PLJS supports the DO block:

DO $$ pljs.elog(NOTICE, 'this', 'is', 'inline', 'code'); $$ LANGUAGE pljs;

IN/OUT/INOUT Handling

There are some specific function declarations that PLJS handles differently than some other procedural languages.

CREATE FUNCTION inout_test(IN t1 TEXT, INOUT i1 INTEGER, OUT o1 TEXT) AS
$$
  return { i1: 23, o1: t1 + i1, foo: 'bar' };
$$
LANGUAGE pljs;

When we execute this we call it with only parameters that are inputs to the function, in this case t1 and i1. Note that only named parameters in the function definition get returned.

SELECT * FROM inout_test('hello', 5);
 i1 |   o1
----+--------
 23 | hello5
(1 row)

When only one variable occurs in the function definition that as an output, then the return type must be a scalar value.

CREATE FUNCTION scalar_test(INOUT i1 INTEGER) AS
$$
  return i1 + 5;
$$
LANGUAGE pljs;

SELECT * FROM scalar_test(23);
i1
----
28
(1 row)

Procedures

Procedures work similarly to functions.

CREATE PROCEDURE procedure_inout_test(IN t1 TEXT, INOUT i1 INTEGER, OUT o1 TEXT) AS
$$
  return { i1: 23, o1: t1 + i1, foo: 'bar' };
$$
LANGUAGE pljs;

The main difference is that OUT arguments must be explicitly used as part of the CALL.

CALL procedure_inout_test('hello', 5, 'foo');
 i1 |   o1
----+--------
 23 | hello5
(1 row)

Again, extraneous output is ignored.