-- CREATE FUNCTION CREATE FUNCTION plv8_test(keys text[], vals text[]) RETURNS text AS $$ var o = {}; for (var i = 0; i < keys.length; i++) o[keys[i]] = vals[i]; return JSON.stringify(o); $$ LANGUAGE plv8 IMMUTABLE STRICT; SELECT plv8_test(ARRAY['name', 'age'], ARRAY['Tom', '29']); CREATE FUNCTION unnamed_args(text[], text[]) RETURNS text[] AS $$ var array1 = arguments[0]; var array2 = $2; return array1.concat(array2); $$ LANGUAGE plv8 IMMUTABLE STRICT; SELECT unnamed_args(ARRAY['A', 'B'], ARRAY['C', 'D']); CREATE FUNCTION concat_strings(VARIADIC args text[]) RETURNS text AS $$ var result = ""; for (var i = 0; i < args.length; i++) if (args[i] != null) result += args[i]; return result; $$ LANGUAGE plv8 IMMUTABLE STRICT; SELECT concat_strings('A', 'B', NULL, 'C'); CREATE FUNCTION return_void() RETURNS void AS $$ $$ LANGUAGE plv8; SELECT return_void(); CREATE FUNCTION return_null() RETURNS text AS $$ return null; $$ LANGUAGE plv8; SELECT r, r IS NULL AS isnull FROM return_null() AS r; -- TYPE CONVERTIONS CREATE FUNCTION int2_to_int4(x int2) RETURNS int4 AS $$ return x; $$ LANGUAGE plv8; SELECT int2_to_int4(24::int2); CREATE FUNCTION int4_to_int2(x int4) RETURNS int2 AS $$ return x; $$ LANGUAGE plv8; SELECT int4_to_int2(42); CREATE FUNCTION int4_to_int8(x int4) RETURNS int8 AS $$ return x; $$ LANGUAGE plv8; SELECT int4_to_int8(48); CREATE FUNCTION int8_to_int4(x int8) RETURNS int4 AS $$ return x; $$ LANGUAGE plv8; SELECT int8_to_int4(84); CREATE FUNCTION float8_to_numeric(x float8) RETURNS numeric AS $$ return x; $$ LANGUAGE plv8; SELECT float8_to_numeric(1.5); CREATE FUNCTION numeric_to_int8(x numeric) RETURNS int8 AS $$ return x; $$ LANGUAGE plv8; SELECT numeric_to_int8(1234.56); CREATE FUNCTION int4_to_text(x int4) RETURNS text AS $$ return x; $$ LANGUAGE plv8; SELECT int4_to_text(123); CREATE FUNCTION text_to_int4(x text) RETURNS int4 AS $$ return x; $$ LANGUAGE plv8; SELECT text_to_int4('123'); SELECT text_to_int4('abc'); -- error CREATE FUNCTION int4array_to_textarray(x int4[]) RETURNS text[] AS $$ return x; $$ LANGUAGE plv8; SELECT int4array_to_textarray(ARRAY[123, 456]::int4[]); CREATE FUNCTION textarray_to_int4array(x text[]) RETURNS int4[] AS $$ return x; $$ LANGUAGE plv8; SELECT textarray_to_int4array(ARRAY['123', '456']::text[]); CREATE FUNCTION timestamptz_to_text(t timestamptz) RETURNS text AS $$ return t.toUTCString() $$ LANGUAGE plv8; SELECT timestamptz_to_text('23 Dec 2010 12:34:56 GMT'); CREATE FUNCTION text_to_timestamptz(t text) RETURNS timestamptz AS $$ return new Date(t) $$ LANGUAGE plv8; SELECT text_to_timestamptz('23 Dec 2010 12:34:56 GMT') AT TIME ZONE 'GMT'; CREATE FUNCTION date_to_text(t date) RETURNS text AS $$ return t.toUTCString() $$ LANGUAGE plv8; SELECT date_to_text('23 Dec 2010'); CREATE FUNCTION text_to_date(t text) RETURNS date AS $$ return new Date(t) $$ LANGUAGE plv8; SELECT text_to_date('23 Dec 2010 GMT'); CREATE FUNCTION oidfn(id oid) RETURNS oid AS $$ return id $$ LANGUAGE plv8; SELECT oidfn('pg_catalog.pg_class'::regclass); -- RECORD TYPES CREATE TYPE rec AS (i integer, t text); CREATE FUNCTION scalar_to_record(i integer, t text) RETURNS rec AS $$ return { "i": i, "t": t }; $$ LANGUAGE plv8; SELECT scalar_to_record(1, 'a'); CREATE FUNCTION record_to_text(x rec) RETURNS text AS $$ return JSON.stringify(x); $$ LANGUAGE plv8; SELECT record_to_text('(1,a)'::rec); CREATE FUNCTION return_record(i integer, t text) RETURNS record AS $$ return { "i": i, "t": t }; $$ LANGUAGE plv8; SELECT * FROM return_record(1, 'a'); SELECT * FROM return_record(1, 'a') AS t(j integer, s text); SELECT * FROM return_record(1, 'a') AS t(x text, y text); CREATE FUNCTION set_of_records() RETURNS SETOF rec AS $$ plv8.return_next( { "i": 1, "t": "a" } ); plv8.return_next( { "i": 2, "t": "b" } ); plv8.return_next( { "i": 3, "t": "c" } ); $$ LANGUAGE plv8; SELECT * FROM set_of_records(); CREATE FUNCTION set_of_record_but_non_obj() RETURNS SETOF rec AS $$ plv8.return_next( "abc" ); $$ LANGUAGE plv8; SELECT * FROM set_of_record_but_non_obj(); CREATE FUNCTION set_of_integers() RETURNS SETOF integer AS $$ plv8.return_next( 1 ); plv8.return_next( 2 ); plv8.return_next( 3 ); $$ LANGUAGE plv8; SELECT * FROM set_of_integers(); CREATE FUNCTION set_of_nest() RETURNS SETOF float AS $$ plv8.return_next( -0.2 ); var rows = plv8.execute( "SELECT set_of_integers() AS i" ); plv8.return_next( rows[0].i ); return 0.2; $$ LANGUAGE plv8; SELECT * FROM set_of_nest(); CREATE FUNCTION set_of_unnamed_records() RETURNS SETOF record AS $$ return [ { i: true } ]; $$ LANGUAGE plv8; SELECT set_of_unnamed_records(); SELECT * FROM set_of_unnamed_records() t (i bool); -- INOUT and OUT parameters CREATE FUNCTION one_inout(a integer, INOUT b text) AS $$ return a + b; $$ LANGUAGE plv8; SELECT one_inout(5, 'ABC'); CREATE FUNCTION one_out(OUT o text, i integer) AS $$ return 'ABC' + i; $$ LANGUAGE plv8; SELECT one_out(123); -- elog() CREATE FUNCTION test_elog(arg text) RETURNS void AS $$ plv8.elog(NOTICE, 'args =', arg); plv8.elog(WARNING, 'warning'); try{ plv8.elog(ERROR, 'ERROR'); }catch(e){ plv8.elog(INFO, e); } plv8.elog(21, 'FATAL is not allowed'); $$ LANGUAGE plv8; SELECT test_elog('ABC'); -- execute() CREATE TABLE test_tbl (i integer, s text); CREATE FUNCTION test_sql() RETURNS integer AS $$ var rows = plv8.execute("SELECT i, 's' || i AS s FROM generate_series(1, 4) AS t(i)"); for (var r = 0; r < rows.length; r++) { var result = plv8.execute("INSERT INTO test_tbl VALUES(" + rows[r].i + ",'" + rows[r].s + "')"); plv8.elog(NOTICE, JSON.stringify(rows[r]), result); } return rows.length; $$ LANGUAGE plv8; SELECT test_sql(); SELECT * FROM test_tbl; CREATE FUNCTION return_sql() RETURNS SETOF test_tbl AS $$ return plv8.execute( "SELECT i, $1 || i AS s FROM generate_series(1, $2) AS t(i)", [ 's', 4 ] ); $$ LANGUAGE plv8; SELECT * FROM return_sql(); CREATE FUNCTION test_sql_error() RETURNS void AS $$ plv8.execute("ERROR") $$ LANGUAGE plv8; SELECT test_sql_error(); CREATE FUNCTION catch_sql_error() RETURNS void AS $$ try { plv8.execute("throw SQL error"); plv8.elog(NOTICE, "should not come here"); } catch (e) { plv8.elog(NOTICE, e); } $$ LANGUAGE plv8; SELECT catch_sql_error(); CREATE FUNCTION catch_sql_error_2() RETURNS text AS $$ try { plv8.execute("throw SQL error"); plv8.elog(NOTICE, "should not come here"); } catch (e) { plv8.elog(NOTICE, e); return plv8.execute("select 'and can execute queries again' t").shift().t; } $$ LANGUAGE plv8; SELECT catch_sql_error_2(); -- subtransaction() CREATE TABLE subtrant(a int); CREATE FUNCTION test_subtransaction_catch() RETURNS void AS $$ try { plv8.subtransaction(function(){ plv8.execute("INSERT INTO subtrant VALUES(1)"); plv8.execute("INSERT INTO subtrant VALUES(1/0)"); }); } catch (e) { plv8.elog(NOTICE, e); plv8.execute("INSERT INTO subtrant VALUES(2)"); } $$ LANGUAGE plv8; SELECT test_subtransaction_catch(); SELECT * FROM subtrant; TRUNCATE subtrant; CREATE FUNCTION test_subtransaction_throw() RETURNS void AS $$ plv8.subtransaction(function(){ plv8.execute("INSERT INTO subtrant VALUES(1)"); plv8.execute("INSERT INTO subtrant VALUES(1/0)"); }); $$ LANGUAGE plv8; SELECT test_subtransaction_throw(); SELECT * FROM subtrant; -- REPLACE FUNCTION CREATE FUNCTION replace_test() RETURNS integer AS $$ return 1; $$ LANGUAGE plv8; SELECT replace_test(); CREATE OR REPLACE FUNCTION replace_test() RETURNS integer AS $$ return 2; $$ LANGUAGE plv8; SELECT replace_test(); -- TRIGGER CREATE FUNCTION test_trigger() RETURNS trigger AS $$ plv8.elog(NOTICE, "NEW = ", JSON.stringify(NEW)); plv8.elog(NOTICE, "OLD = ", JSON.stringify(OLD)); plv8.elog(NOTICE, "TG_OP = ", TG_OP); plv8.elog(NOTICE, "TG_ARGV = ", TG_ARGV); if (TG_OP == "UPDATE") { NEW.i = 102; return NEW; } $$ LANGUAGE "plv8"; CREATE TRIGGER test_trigger BEFORE INSERT OR UPDATE OR DELETE ON test_tbl FOR EACH ROW EXECUTE PROCEDURE test_trigger('foo', 'bar'); INSERT INTO test_tbl VALUES(100, 'ABC'); UPDATE test_tbl SET i = 101, s = 'DEF' WHERE i = 1; DELETE FROM test_tbl WHERE i >= 100; SELECT * FROM test_tbl; -- ERRORS CREATE FUNCTION syntax_error() RETURNS text AS '@' LANGUAGE plv8; CREATE FUNCTION reference_error() RETURNS text AS 'not_defined' LANGUAGE plv8; SELECT reference_error(); CREATE FUNCTION throw() RETURNS void AS $$throw new Error("an error");$$ LANGUAGE plv8; SELECT throw(); -- SPI operations CREATE FUNCTION prep1() RETURNS void AS $$ var plan = plv8.prepare("SELECT * FROM test_tbl"); plv8.elog(INFO, plan.toString()); var rows = plan.execute(); for(var i = 0; i < rows.length; i++) { plv8.elog(INFO, JSON.stringify(rows[i])); } var cursor = plan.cursor(); plv8.elog(INFO, cursor.toString()); var row; while(row = cursor.fetch()) { plv8.elog(INFO, JSON.stringify(row)); } cursor.close(); plan.free(); var plan = plv8.prepare("SELECT * FROM test_tbl WHERE i = $1 and s = $2", ["int", "text"]); var rows = plan.execute([2, "s2"]); plv8.elog(INFO, "rows.length = ", rows.length); var cursor = plan.cursor([2, "s2"]); plv8.elog(INFO, JSON.stringify(cursor.fetch())); cursor.close(); plan.free(); try{ var plan = plv8.prepare("SELECT * FROM test_tbl"); plan.free(); plan.execute(); }catch(e){ plv8.elog(WARNING, e); } try{ var plan = plv8.prepare("SELECT * FROM test_tbl"); var cursor = plan.cursor(); cursor.close(); cursor.fetch(); }catch(e){ plv8.elog(WARNING, e); } $$ LANGUAGE plv8 STRICT; SELECT prep1(); -- find_function CREATE FUNCTION callee(a int) RETURNS int AS $$ return a * a $$ LANGUAGE plv8; CREATE FUNCTION sqlf(int) RETURNS int AS $$ SELECT $1 * $1 $$ LANGUAGE sql; CREATE FUNCTION caller(a int, t int) RETURNS int AS $$ var func; if (t == 1) { func = plv8.find_function("callee"); } else if (t == 2) { func = plv8.find_function("callee(int)"); } else if (t == 3) { func = plv8.find_function("sqlf"); } else if (t == 4) { func = plv8.find_function("callee(int, int)"); } else if (t == 5) { try{ func = plv8.find_function("caller()"); }catch(e){ func = function(a){ return a }; } } return func(a); $$ LANGUAGE plv8; SELECT caller(10, 1); SELECT caller(10, 2); SELECT caller(10, 3); SELECT caller(10, 4); SELECT caller(10, 5); -- quote_* CREATE FUNCTION plv8_quotes(s text) RETURNS text AS $$ return [plv8.quote_literal(s), plv8.quote_nullable(s), plv8.quote_ident(s)].join(":"); $$ LANGUAGE plv8 IMMUTABLE; SELECT plv8_quotes('select'); SELECT plv8_quotes('kevin''s name'); SELECT plv8_quotes(NULL);