Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
branch: master
Fetching contributors…

Cannot retrieve contributors at this time

383 lines (350 sloc) 9.569 kb
-- 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']);
plv8_test
---------------------------
{"name":"Tom","age":"29"}
(1 row)
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']);
unnamed_args
--------------
{A,B,C,D}
(1 row)
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');
concat_strings
----------------
ABC
(1 row)
CREATE FUNCTION return_void() RETURNS void AS $$ $$ LANGUAGE plv8;
SELECT return_void();
return_void
-------------
(1 row)
CREATE FUNCTION return_null() RETURNS text AS $$ return null; $$ LANGUAGE plv8;
SELECT r, r IS NULL AS isnull FROM return_null() AS r;
r | isnull
---+--------
| t
(1 row)
-- TYPE CONVERTIONS
CREATE FUNCTION int2_to_int4(x int2) RETURNS int4 AS $$ return x; $$ LANGUAGE plv8;
SELECT int2_to_int4(24::int2);
int2_to_int4
--------------
24
(1 row)
CREATE FUNCTION int4_to_int2(x int4) RETURNS int2 AS $$ return x; $$ LANGUAGE plv8;
SELECT int4_to_int2(42);
int4_to_int2
--------------
42
(1 row)
CREATE FUNCTION int4_to_int8(x int4) RETURNS int8 AS $$ return x; $$ LANGUAGE plv8;
SELECT int4_to_int8(48);
int4_to_int8
--------------
48
(1 row)
CREATE FUNCTION int8_to_int4(x int8) RETURNS int4 AS $$ return x; $$ LANGUAGE plv8;
SELECT int8_to_int4(84);
int8_to_int4
--------------
84
(1 row)
CREATE FUNCTION float8_to_numeric(x float8) RETURNS numeric AS $$ return x; $$ LANGUAGE plv8;
SELECT float8_to_numeric(1.5);
float8_to_numeric
-------------------
1.5
(1 row)
CREATE FUNCTION numeric_to_int8(x numeric) RETURNS int8 AS $$ return x; $$ LANGUAGE plv8;
SELECT numeric_to_int8(1234.56);
numeric_to_int8
-----------------
1234
(1 row)
CREATE FUNCTION int4_to_text(x int4) RETURNS text AS $$ return x; $$ LANGUAGE plv8;
SELECT int4_to_text(123);
int4_to_text
--------------
123
(1 row)
CREATE FUNCTION text_to_int4(x text) RETURNS int4 AS $$ return x; $$ LANGUAGE plv8;
SELECT text_to_int4('123');
text_to_int4
--------------
123
(1 row)
SELECT text_to_int4('abc'); -- error
ERROR: invalid input syntax for integer: "abc"
CREATE FUNCTION int4array_to_textarray(x int4[]) RETURNS text[] AS $$ return x; $$ LANGUAGE plv8;
SELECT int4array_to_textarray(ARRAY[123, 456]::int4[]);
int4array_to_textarray
------------------------
{123,456}
(1 row)
CREATE FUNCTION textarray_to_int4array(x text[]) RETURNS int4[] AS $$ return x; $$ LANGUAGE plv8;
SELECT textarray_to_int4array(ARRAY['123', '456']::text[]);
textarray_to_int4array
------------------------
{123,456}
(1 row)
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');
timestamptz_to_text
-------------------------------
Thu, 23 Dec 2010 12:34:56 GMT
(1 row)
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';
timezone
--------------------------
Thu Dec 23 12:34:56 2010
(1 row)
CREATE FUNCTION date_to_text(t date) RETURNS text AS $$ return t.toUTCString() $$ LANGUAGE plv8;
SELECT date_to_text('23 Dec 2010');
date_to_text
-------------------------------
Thu, 23 Dec 2010 00:00:00 GMT
(1 row)
CREATE FUNCTION text_to_date(t text) RETURNS date AS $$ return new Date(t) $$ LANGUAGE plv8;
SELECT text_to_date('23 Dec 2010 GMT');
text_to_date
--------------
12-23-2010
(1 row)
CREATE FUNCTION oidfn(id oid) RETURNS oid AS $$ return id $$ LANGUAGE plv8;
SELECT oidfn('pg_catalog.pg_class'::regclass);
oidfn
-------
1259
(1 row)
-- 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');
scalar_to_record
------------------
(1,a)
(1 row)
CREATE FUNCTION record_to_text(x rec) RETURNS text AS
$$
return JSON.stringify(x);
$$
LANGUAGE plv8;
SELECT record_to_text('(1,a)'::rec);
record_to_text
-----------------
{"i":1,"t":"a"}
(1 row)
CREATE FUNCTION return_record(i integer, t text) RETURNS record AS
$$
return { "i": i, "t": t };
$$
LANGUAGE plv8;
SELECT * FROM return_record(1, 'a');
ERROR: a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM return_record(1, 'a');
^
SELECT * FROM return_record(1, 'a') AS t(j integer, s text);
ERROR: input of anonymous composite types is not implemented
SELECT * FROM return_record(1, 'a') AS t(x text, y text);
ERROR: input of anonymous composite types is not implemented
CREATE FUNCTION set_of_records() RETURNS SETOF rec AS
$$
yield( { "i": 1, "t": "a" } );
yield( { "i": 2, "t": "b" } );
yield( { "i": 3, "t": "c" } );
$$
LANGUAGE plv8;
SELECT * FROM set_of_records();
i | t
---+---
1 | a
2 | b
3 | c
(3 rows)
CREATE FUNCTION set_of_integers() RETURNS SETOF integer AS
$$
yield( 1 );
yield( 2 );
yield( 3 );
$$
LANGUAGE plv8;
SELECT * FROM set_of_integers();
ERROR: SETOF a scalar type is not implemented yet
-- INOUT and OUT parameters
CREATE FUNCTION one_inout(a integer, INOUT b text) AS
$$
return a + b;
$$
LANGUAGE plv8;
SELECT one_inout(5, 'ABC');
one_inout
-----------
5ABC
(1 row)
CREATE FUNCTION one_out(OUT o text, i integer) AS
$$
return 'ABC' + i;
$$
LANGUAGE plv8;
SELECT one_out(123);
one_out
---------
ABC123
(1 row)
-- print()
CREATE FUNCTION test_print(arg text) RETURNS void AS
$$
print(NOTICE, 'args =', arg);
print(WARNING, 'warning');
print(20, 'ERROR is not allowed');
$$
LANGUAGE plv8;
SELECT test_print('ABC');
NOTICE: args = ABC
WARNING: warning
ERROR: Error: invalid error level for print()
DETAIL: test_print() LINE 4: print(20, 'ERROR is not allowed');
-- executeSql()
CREATE TABLE test_tbl (i integer, s text);
CREATE FUNCTION test_sql() RETURNS integer AS
$$
var rows = executeSql("SELECT i, 's' || i AS s FROM generate_series(1, 4) AS t(i)");
for (var r = 0; r < rows.length; r++)
{
var result = executeSql("INSERT INTO test_tbl VALUES(" + rows[r].i + ",'" + rows[r].s + "')");
print(NOTICE, JSON.stringify(rows[r]), result);
}
return rows.length;
$$
LANGUAGE plv8;
SELECT test_sql();
NOTICE: {"i":1,"s":"s1"} 1
NOTICE: {"i":2,"s":"s2"} 1
NOTICE: {"i":3,"s":"s3"} 1
NOTICE: {"i":4,"s":"s4"} 1
test_sql
----------
4
(1 row)
SELECT * FROM test_tbl;
i | s
---+----
1 | s1
2 | s2
3 | s3
4 | s4
(4 rows)
CREATE FUNCTION return_sql() RETURNS SETOF test_tbl AS
$$
return executeSql(
"SELECT i, $1 || i AS s FROM generate_series(1, $2) AS t(i)",
[ 's', 4 ]
);
$$
LANGUAGE plv8;
SELECT * FROM return_sql();
i | s
---+----
1 | s1
2 | s2
3 | s3
4 | s4
(4 rows)
CREATE FUNCTION test_sql_error() RETURNS void AS $$ executeSql("ERROR") $$ LANGUAGE plv8;
SELECT test_sql_error();
ERROR: Error: syntax error at or near "ERROR"
DETAIL: test_sql_error() LINE 1: executeSql("ERROR")
-- REPLACE FUNCTION
CREATE FUNCTION replace_test() RETURNS integer AS $$ return 1; $$ LANGUAGE plv8;
SELECT replace_test();
replace_test
--------------
1
(1 row)
CREATE OR REPLACE FUNCTION replace_test() RETURNS integer AS $$ return 2; $$ LANGUAGE plv8;
SELECT replace_test();
replace_test
--------------
2
(1 row)
-- TRIGGER
CREATE FUNCTION test_trigger() RETURNS trigger AS
$$
print(NOTICE, "NEW = ", JSON.stringify(NEW));
print(NOTICE, "OLD = ", JSON.stringify(OLD));
print(NOTICE, "TG_OP = ", TG_OP);
print(NOTICE, "TG_ARGV = ", TG_ARGV);
$$
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');
NOTICE: NEW = {"i":100,"s":"ABC"}
NOTICE: OLD = undefined
NOTICE: TG_OP = INSERT
NOTICE: TG_ARGV = foo,bar
UPDATE test_tbl SET i = 101, s = 'DEF' WHERE i = 1;
NOTICE: NEW = {"i":101,"s":"DEF"}
NOTICE: OLD = {"i":1,"s":"s1"}
NOTICE: TG_OP = UPDATE
NOTICE: TG_ARGV = foo,bar
DELETE FROM test_tbl WHERE i >= 100;
NOTICE: NEW = undefined
NOTICE: OLD = {"i":100,"s":"ABC"}
NOTICE: TG_OP = DELETE
NOTICE: TG_ARGV = foo,bar
NOTICE: NEW = undefined
NOTICE: OLD = {"i":101,"s":"DEF"}
NOTICE: TG_OP = DELETE
NOTICE: TG_ARGV = foo,bar
SELECT * FROM test_tbl;
i | s
---+----
2 | s2
3 | s3
4 | s4
(3 rows)
-- ERRORS
CREATE FUNCTION syntax_error() RETURNS text AS '@' LANGUAGE plv8;
ERROR: SyntaxError: Unexpected token ILLEGAL
DETAIL: syntax_error() LINE 1: @
CREATE FUNCTION reference_error() RETURNS text AS 'not_defined' LANGUAGE plv8;
SELECT reference_error();
ERROR: ReferenceError: not_defined is not defined
DETAIL: reference_error() LINE 1: not_defined
CREATE FUNCTION throw() RETURNS void AS $$throw new Error("an error");$$ LANGUAGE plv8;
SELECT throw();
ERROR: Error: an error
DETAIL: throw() LINE 1: throw new Error("an error");
Jump to Line
Something went wrong with that request. Please try again.