-- plxjs regression tests CREATE EXTENSION IF NOT EXISTS plx; NOTICE: extension "plx" already exists, skipping SET client_min_messages = warning; -- scalar return CREATE FUNCTION js_add(a int, b int) RETURNS int LANGUAGE plxjs AS $$ return a + b * 2; $$; SELECT js_add(3, 4); js_add -------- 11 (1 row) -- let, if / else if / else CREATE FUNCTION js_grade(score int) RETURNS text LANGUAGE plxjs AS $$ let grade = "F"; if (score >= 90) { grade = "A"; } else if (score >= 80) { grade = "B"; } else { grade = "F"; } return grade; $$; SELECT js_grade(95), js_grade(85), js_grade(70); js_grade | js_grade | js_grade ----------+----------+---------- A | B | F (1 row) -- C-style for with let CREATE FUNCTION js_sum(n int) RETURNS int LANGUAGE plxjs AS $$ let total = 0; for (let i = 1; i <= n; i++) { total = total + i; } return total; $$; SELECT js_sum(10); js_sum -------- 55 (1 row) -- template literal interpolation CREATE FUNCTION js_greet(nm text) RETURNS text LANGUAGE plxjs AS $$ return `Hello, ${nm}!`; $$; SELECT js_greet('World'); js_greet --------------- Hello, World! (1 row) CREATE FUNCTION js_interp(nm text, n int) RETURNS text LANGUAGE plxjs AS $$ return `user ${nm} has ${n} items`; $$; SELECT js_interp('bob', 3); js_interp ---------------------- user bob has 3 items (1 row) -- while CREATE FUNCTION js_countdown(n int) RETURNS int LANGUAGE plxjs AS $$ let c = 0; while (n > 0) { c = c + n; n = n - 1; } return c; $$; SELECT js_countdown(5); js_countdown -------------- 15 (1 row) -- for-of over a query CREATE TABLE js_orders(grp int, amount bigint); INSERT INTO js_orders VALUES (1,10),(1,20),(1,30),(2,5); CREATE FUNCTION js_grp_total(g int) RETURNS bigint LANGUAGE plxjs AS $$ let total = 0 /*:: bigint */; for (const row of query(`SELECT amount FROM js_orders WHERE grp = ${g}`)) { total = total + row.amount; } return total; $$; SELECT js_grp_total(1), js_grp_total(2); js_grp_total | js_grp_total --------------+-------------- 60 | 5 (1 row) -- fetch_one CREATE TABLE js_users(id int, name text); INSERT INTO js_users VALUES (1,'Alice'),(2,'Bob'); CREATE FUNCTION js_name(uid int) RETURNS text LANGUAGE plxjs AS $$ let u = fetch_one(`SELECT id, name FROM js_users WHERE id = ${uid}`); return u.name; $$; SELECT js_name(2); js_name --------- Bob (1 row) -- SETOF via return_next CREATE FUNCTION js_squares(n int) RETURNS SETOF int LANGUAGE plxjs AS $$ for (let i = 1; i <= n; i++) { return_next(i * i); } return; $$; SELECT * FROM js_squares(3); js_squares ------------ 1 4 9 (3 rows) -- try / catch CREATE FUNCTION js_safediv(d int) RETURNS int LANGUAGE plxjs AS $$ try { return 100 / d; } catch (e) { raise("notice", `caught: ${e.message}`); return -1; } $$; SELECT js_safediv(4), js_safediv(0); js_safediv | js_safediv ------------+------------ 25 | -1 (1 row) -- throw CREATE FUNCTION js_checkpos(v int) RETURNS int LANGUAGE plxjs AS $$ if (v < 0) { throw new Error(`negative: ${v}`); } return v; $$; SELECT js_checkpos(7); js_checkpos ------------- 7 (1 row) -- DO block (body is JavaScript) DO LANGUAGE plxjs $$ for (let i = 1; i <= 2; i++) { raise("warning", `row ${i}`); } $$; WARNING: row 1 WARNING: row 2 -- switch (with stacked cases and default) CREATE FUNCTION js_switch(n int) RETURNS text LANGUAGE plxjs AS $$ switch (n) { case 1: return "one"; case 2: case 3: return "few"; default: return "many"; } $$; SELECT js_switch(1), js_switch(2), js_switch(9); js_switch | js_switch | js_switch -----------+-----------+----------- one | few | many (1 row) -- assert CREATE FUNCTION js_assert(n int) RETURNS int LANGUAGE plxjs AS $$ assert(n > 0, "must be positive"); return n; $$; SELECT js_assert(5); js_assert ----------- 5 (1 row) -- FOREACH over an array (for..of) CREATE FUNCTION js_sumarr(a int[]) RETURNS int LANGUAGE plxjs AS $$ let total = 0 /*:: int */; let v /*:: int */; for (const v of a) { total = total + v; } return total; $$; SELECT js_sumarr(ARRAY[1,2,3,4]); js_sumarr ----------- 10 (1 row) -- cursors CREATE TABLE js_nums(v int); INSERT INTO js_nums SELECT generate_series(1,4); CREATE FUNCTION js_cursor_sum() RETURNS int LANGUAGE plxjs AS $$ let total = 0 /*:: int */; let c = open_cursor(`SELECT v FROM js_nums ORDER BY v`); let row = fetch_from(c); while (found()) { total = total + row.v; row = fetch_from(c); } close_cursor(c); return total; $$; SELECT js_cursor_sum(); js_cursor_sum --------------- 10 (1 row) -- labeled loop CREATE FUNCTION js_labeled(n int) RETURNS int LANGUAGE plxjs AS $$ let count = 0 /*:: int */; outer: for (let i = 1; i <= n; i++) { for (let j = 1; j <= n; j++) { count = count + 1; if (i + j >= 4) { break outer; } } } return count; $$; SELECT js_labeled(5); js_labeled ------------ 3 (1 row) -- non-decimal integer literals (hex/binary/octal, with '_' groups) convert to -- decimal so the generated plpgsql is portable to PG13-15 (no 0x/0o/0b there) CREATE FUNCTION js_hex() RETURNS int LANGUAGE plxjs AS $$ return 0xFF; $$; CREATE FUNCTION js_bin() RETURNS int LANGUAGE plxjs AS $$ return 0b1010; $$; CREATE FUNCTION js_oct() RETURNS int LANGUAGE plxjs AS $$ return 0o17; $$; CREATE FUNCTION js_hex_us() RETURNS int LANGUAGE plxjs AS $$ return 0xFF_FF; $$; SELECT js_hex() AS hex, js_bin() AS bin, js_oct() AS oct, js_hex_us() AS hex_us; hex | bin | oct | hex_us -----+-----+-----+-------- 255 | 10 | 15 | 65535 (1 row) -- float and exponent literals are unaffected CREATE FUNCTION js_flt() RETURNS numeric LANGUAGE plxjs AS $$ return 3.14; $$; CREATE FUNCTION js_exp() RETURNS numeric LANGUAGE plxjs AS $$ return 1.5e3; $$; SELECT js_flt() AS flt, js_exp() AS exp; flt | exp ------+------ 3.14 | 1500 (1 row) -- a type annotation at the start of a condition is ignored (it used to give a -- span with no source text and crash the backend) CREATE FUNCTION js_ann_cond(x int) RETURNS int LANGUAGE plxjs AS $$ if (/*:: int */ x > 0) { return 1; } return 0; $$; SELECT js_ann_cond(3), js_ann_cond(-3); js_ann_cond | js_ann_cond -------------+------------- 1 | 0 (1 row) -- finally inside finally is emitted once per level, so deep nesting stays -- linear in size (each level used to double the generated body) DO $d$ DECLARE s text := 'x = 1;'; BEGIN FOR i IN 1..40 LOOP s := 'try { x = x + 1; } finally { ' || s || ' }'; END LOOP; EXECUTE 'CREATE FUNCTION js_fin_deep() RETURNS int LANGUAGE plxjs AS $b$ let x = 0 /*:: int */; ' || s || ' return x; $b$'; END $d$; SELECT js_fin_deep(), length(prosrc) < 200000 AS linear FROM pg_proc WHERE proname = 'js_fin_deep'; js_fin_deep | linear -------------+-------- 1 | t (1 row) -- a return at every level of deeply nested try/finally: the generated code -- stays linear in the source (indentation is capped), and each level's text is -- freed once emitted (it used to be kept, which took 3.6 GB at 490 levels) DO $d$ DECLARE s text := 'x = 1;'; BEGIN FOR i IN 1..300 LOOP s := 'try { if (x > 5) { return 1; } ' || s || ' } finally { x = x + 1; }'; IF i IN (150, 300) THEN EXECUTE 'CREATE FUNCTION js_fin_ret_deep' || i || '() RETURNS int LANGUAGE plxjs AS $b$ let x = 0 /*:: int */; ' || s || ' return x; $b$'; END IF; END LOOP; END $d$; -- twice the depth gives about twice the code (2.06 times: the first levels are -- less indented); growing indentation made it over three times SELECT js_fin_ret_deep300(), (SELECT length(prosrc) FROM pg_proc WHERE proname = 'js_fin_ret_deep300') < 2.5 * (SELECT length(prosrc) FROM pg_proc WHERE proname = 'js_fin_ret_deep150') AS linear; js_fin_ret_deep300 | linear --------------------+-------- 301 | t (1 row) -- a finally body that throws runs once, not again from the error path DO LANGUAGE plxjs $p$ try { raise("warning", "body"); } finally { raise("warning", "finally ran"); throw new Exception("boom"); } $p$; WARNING: body WARNING: finally ran ERROR: boom CONTEXT: PL/pgSQL function inline_code_block line 23 at RAISE -- a string ending in a lone backslash cannot escape its quotes: the second -- assignment stays an inert string instead of running set_config CREATE FUNCTION js_bs_escape() RETURNS text LANGUAGE plxjs AS $q$ let a = `${'\n\'}`; let b = " || set_config($x$plx.test_pwned$x$, $x$yes$x$, false)); --"; return b; $q$; SELECT js_bs_escape() LIKE '%set_config%' AS literal_kept, current_setting('plx.test_pwned', true) IS NULL AS not_run; literal_kept | not_run --------------+--------- t | t (1 row) -- a switch with no matching case and no default does nothing (plpgsql's CASE -- would raise case_not_found) CREATE FUNCTION js_sw_nodefault(x int) RETURNS int LANGUAGE plxjs AS $$ let r = 0; switch (x) { case 1: r = 10; break; } return r; $$; SELECT js_sw_nodefault(1), js_sw_nodefault(2); js_sw_nodefault | js_sw_nodefault -----------------+----------------- 10 | 0 (1 row) -- finally runs on return, break and continue, and after an error, once each CREATE FUNCTION js_fin_return() RETURNS int LANGUAGE plxjs AS $$ let x = 1; try { return x; } finally { x = 99; raise("warning", "finally after return"); } $$; SELECT js_fin_return(); -- 1: the value is taken before finally runs WARNING: finally after return js_fin_return --------------- 1 (1 row) CREATE FUNCTION js_fin_loop() RETURNS int LANGUAGE plxjs AS $$ let n = 0; let i = 0; while (i < 10) { i = i + 1; try { if (i == 2) { continue; } if (i == 5) { break; } n = n + i; } finally { n = n + 100; } } return n; $$; SELECT js_fin_loop(); -- 508 js_fin_loop ------------- 508 (1 row) CREATE FUNCTION js_fin_nested() RETURNS text LANGUAGE plxjs AS $$ try { try { return "r"; } finally { raise("warning", "inner"); } } finally { raise("warning", "outer"); } $$; SELECT js_fin_nested(); WARNING: inner WARNING: outer js_fin_nested --------------- r (1 row) CREATE FUNCTION js_fin_label() RETURNS int LANGUAGE plxjs AS $$ let n = 0; outer: for (let i = 0; i < 3; i++) { try { for (let j = 0; j < 3; j++) { if (j == 1) { break; } n = n + 1; } if (i == 1) { break outer; } } finally { n = n + 10; } } return n; $$; SELECT js_fin_label(); -- 22 js_fin_label -------------- 22 (1 row) -- a loop label reused inside the try still crosses the finally: a break or -- continue resolves to the enclosing loop once the inner one has closed CREATE FUNCTION js_fin_label_reuse(c bool) RETURNS int LANGUAGE plxjs AS $$ let r = 0; outer: for (let i = 0; i < 2; i++) { try { outer: for (let j = 0; j < 1; j++) { r = r * 10 + 1; } if (c) { continue outer; } break outer; } finally { r = r * 10 + 9; } } return r; $$; SELECT js_fin_label_reuse(false), js_fin_label_reuse(true); -- 19, 1919 js_fin_label_reuse | js_fin_label_reuse --------------------+-------------------- 19 | 1919 (1 row) -- an error is re-raised after finally with its SQLSTATE, detail and hint CREATE FUNCTION js_fin_fail() RETURNS int LANGUAGE plpgsql AS $$ BEGIN RAISE EXCEPTION USING ERRCODE = '22012', MESSAGE = 'boom', DETAIL = 'd1', HINT = 'h1'; END $$; CREATE FUNCTION js_fin_err() RETURNS int LANGUAGE plxjs AS $$ try { return js_fin_fail(); } finally { raise("warning", "cleanup"); } $$; DO $d$ DECLARE st text; dt text; ht text; BEGIN PERFORM js_fin_err(); EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS st = RETURNED_SQLSTATE, dt = PG_EXCEPTION_DETAIL, ht = PG_EXCEPTION_HINT; RAISE WARNING 'caught: % % % %', st, SQLERRM, dt, ht; END $d$; WARNING: cleanup WARNING: caught: 22012 boom d1 h1 -- ... and with the names of the objects it is about (table, constraint, type) CREATE TABLE js_fin_objt (id int CONSTRAINT js_fin_objt_pk PRIMARY KEY); INSERT INTO js_fin_objt VALUES (1); CREATE DOMAIN js_fin_pos AS int CONSTRAINT js_fin_pos_ck CHECK (VALUE > 0); CREATE FUNCTION js_fin_objs(k int) RETURNS void LANGUAGE plxjs AS $$ try { if (k == 1) { execute("INSERT INTO js_fin_objt VALUES (1)"); } else { execute("SELECT (-1)::js_fin_pos"); } } finally { let z = 1; } $$; DO $d$ DECLARE st text; sc text; tb text; co text; ty text; cl text; BEGIN FOR k IN 1..2 LOOP BEGIN PERFORM js_fin_objs(k); EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS st = RETURNED_SQLSTATE, sc = SCHEMA_NAME, tb = TABLE_NAME, co = CONSTRAINT_NAME, ty = PG_DATATYPE_NAME, cl = COLUMN_NAME; RAISE WARNING 'caught %: schema=% table=% constraint=% type=% column=%', st, sc, tb, co, ty, cl; END; END LOOP; END $d$; WARNING: caught 23505: schema=public table=js_fin_objt constraint=js_fin_objt_pk type= column= WARNING: caught 23514: schema=public table= constraint=js_fin_pos_ck type=js_fin_pos column= -- a return in finally replaces the error; a return from catch runs finally CREATE FUNCTION js_fin_override() RETURNS int LANGUAGE plxjs AS $$ try { return js_fin_fail(); } finally { return 7; } $$; SELECT js_fin_override(); js_fin_override ----------------- 7 (1 row) CREATE FUNCTION js_fin_catch() RETURNS int LANGUAGE plxjs AS $$ try { return js_fin_fail(); } catch (e) { return -1; } finally { raise("warning", "after catch"); } $$; SELECT js_fin_catch(); WARNING: after catch js_fin_catch -------------- -1 (1 row) -- a trigger returning NEW, and a set-returning function's bare return CREATE TABLE js_fin_t(a int); CREATE FUNCTION js_fin_trg() RETURNS trigger LANGUAGE plxjs AS $$ try { NEW.a = NEW.a * 2; return NEW; } finally { raise("warning", "trigger finally"); } $$; CREATE TRIGGER js_fin_t1 BEFORE INSERT ON js_fin_t FOR EACH ROW EXECUTE FUNCTION js_fin_trg(); INSERT INTO js_fin_t VALUES (21); WARNING: trigger finally SELECT * FROM js_fin_t; a ---- 42 (1 row) DROP TABLE js_fin_t CASCADE; CREATE FUNCTION js_fin_srf() RETURNS SETOF int LANGUAGE plxjs AS $$ emit 1; try { emit 2; return; } finally { emit 3; } emit 4; $$; SELECT * FROM js_fin_srf(); js_fin_srf ------------ 1 2 3 (3 rows)