-- plx cross-cutting feature tests: things not tied to one dialect's syntax -- (OUT params, triggers, record-field assignment, idempotency, string escaping, -- semantic edge cases, and DO blocks in every dialect). CREATE EXTENSION IF NOT EXISTS plx; NOTICE: extension "plx" already exists, skipping SET client_min_messages = notice; -- OUT parameters CREATE FUNCTION f_out(a int, b int, OUT sum int, OUT diff int) LANGUAGE plxruby AS $$ sum = a + b diff = a - b $$; SELECT * FROM f_out(10, 3); sum | diff -----+------ 13 | 7 (1 row) -- trigger function assigning to NEW.field (plxruby) CREATE TABLE f_trg(id int, tag text); CREATE FUNCTION f_trg_fn() RETURNS trigger LANGUAGE plxruby AS $$ NEW.tag = "set-#{NEW.id}" return NEW $$; CREATE TRIGGER f_tr BEFORE INSERT ON f_trg FOR EACH ROW EXECUTE FUNCTION f_trg_fn(); INSERT INTO f_trg(id) VALUES (7); SELECT tag FROM f_trg; tag ------- set-7 (1 row) -- trigger function in plxpython3 CREATE TABLE f_trg2(id int, tag text); CREATE FUNCTION f_trg_py() RETURNS trigger LANGUAGE plxpython3 AS $$ NEW.tag = f"py-{NEW.id}" return NEW $$; CREATE TRIGGER f_tr2 BEFORE INSERT ON f_trg2 FOR EACH ROW EXECUTE FUNCTION f_trg_py(); INSERT INTO f_trg2(id) VALUES (9); SELECT tag FROM f_trg2; tag ------ py-9 (1 row) -- CREATE OR REPLACE is idempotent and re-transpiles the new body CREATE FUNCTION f_idem() RETURNS int LANGUAGE plxruby AS $$ return 1 $$; CREATE OR REPLACE FUNCTION f_idem() RETURNS int LANGUAGE plxruby AS $$ return 2 $$; SELECT f_idem(); f_idem -------- 2 (1 row) SELECT prosrc ~ '^/[*]plx:v1:' AS has_sentinel FROM pg_proc WHERE proname = 'f_idem'; has_sentinel -------------- t (1 row) -- string escaping: apostrophe inside interpolation CREATE FUNCTION f_apos(n int) RETURNS text LANGUAGE plxruby AS $$ return "it's #{n} o'clock" $$; SELECT f_apos(9); f_apos ---------------- it's 9 o'clock (1 row) -- escape sequences lower to an E-string CREATE FUNCTION f_esc() RETURNS boolean LANGUAGE plxruby AS $$ return "a\tb\nc" = E'a\tb\nc' $$; SELECT f_esc(); f_esc ------- t (1 row) -- literal percent in a RAISE message is preserved CREATE FUNCTION f_pct() RETURNS void LANGUAGE plxruby AS $$ raise notice: "50% done for #{1 + 1} items" $$; SELECT f_pct(); NOTICE: 50% done for 2 items f_pct ------- (1 row) -- exclusive range yields the exact element set (built via interpolation, since -- + stays numeric) CREATE FUNCTION f_excl() RETURNS text LANGUAGE plxruby AS $$ out = "" #:: text for i in 1...4 out = "#{out}#{i}" end return out $$; SELECT f_excl(); f_excl -------- 123 (1 row) -- SQL three-valued equality: == maps to plain = (not null-aware), so a -- comparison involving NULL is unknown and the condition is not taken. CREATE FUNCTION f_null(a int, b int) RETURNS text LANGUAGE plxruby AS $$ if a == b return "eq" end return "ne" $$; SELECT f_null(NULL, NULL) AS null_vs_null_is_ne, f_null(1, NULL) AS val_vs_null_is_ne, f_null(2, 2) AS eq; null_vs_null_is_ne | val_vs_null_is_ne | eq --------------------+-------------------+---- ne | ne | eq (1 row) -- the literal-nil form is the only null special case CREATE FUNCTION f_isnil(a int) RETURNS text LANGUAGE plxruby AS $$ return a == nil ? "nil" : "notnil" $$; SELECT f_isnil(NULL) AS nil, f_isnil(5) AS notnil; nil | notnil -----+-------- nil | notnil (1 row) -- DO blocks in every dialect DO LANGUAGE plxruby $$ raise notice: "ruby do #{2 * 3}" $$; NOTICE: ruby do 6 DO LANGUAGE plxphp $$ raise('notice', 'php do ' . (2 * 3)); $$; NOTICE: php do 6 DO LANGUAGE plxjs $$ raise("notice", `js do ${2 * 3}`); $$; NOTICE: js do 6 DO LANGUAGE plxpython3 $$ raise('notice', f'py do {2 * 3}') $$; NOTICE: py do 6 -- interpolating a NULL propagates it, so the whole value is NULL in every -- dialect, the way SQL || behaves CREATE FUNCTION f_interp_rb(x int) RETURNS text LANGUAGE plxruby AS $$ return "[#{x}]" $$; CREATE FUNCTION f_interp_php(x int) RETURNS text LANGUAGE plxphp AS $$ return "[{$x}]"; $$; CREATE FUNCTION f_interp_js(x int) RETURNS text LANGUAGE plxjs AS $$ return `[${x}]`; $$; CREATE FUNCTION f_interp_py(x int) RETURNS text LANGUAGE plxpython3 AS $$ return f"[{x}]" $$; SELECT f_interp_rb(NULL) IS NULL AS rb, f_interp_php(NULL) IS NULL AS php, f_interp_js(NULL) IS NULL AS js, f_interp_py(NULL) IS NULL AS py; rb | php | js | py ----+-----+----+---- t | t | t | t (1 row) SELECT f_interp_rb(7) AS rb7; rb7 ----- [7] (1 row) -- but a message built for RAISE keeps each value as an empty string, so one -- NULL cannot swallow the text the message was written to carry CREATE FUNCTION f_msg_rb(x int) RETURNS void LANGUAGE plxruby AS $$ raise notice: "value is [#{x}] here" $$; CREATE FUNCTION f_msg_py(x int) RETURNS void LANGUAGE plxpython3 AS $$ raise('notice', f'value is [{x}] here') $$; SELECT f_msg_rb(7), f_msg_rb(NULL); NOTICE: value is [7] here NOTICE: value is [] here f_msg_rb | f_msg_rb ----------+---------- | (1 row) SELECT f_msg_py(7), f_msg_py(NULL); NOTICE: value is [7] here NOTICE: value is [] here f_msg_py | f_msg_py ----------+---------- | (1 row) -- OUT / INOUT parameters (audit): supported across dialects CREATE FUNCTION f_out(a int, OUT b int, OUT c int) LANGUAGE plxruby AS $$ b = a * 2 c = a + 1 $$; SELECT b, c FROM f_out(5); b | c ----+--- 10 | 6 (1 row) CREATE FUNCTION f_inout(INOUT x int) LANGUAGE plxphp AS $$ $x = $x + 100; $$; SELECT f_inout(1); f_inout --------- 101 (1 row) -- RETURNS TABLE: the table columns are OUT parameters, so emit a row with a bare -- emit / return_next (an argument form is rejected by plpgsql here) CREATE FUNCTION f_table(n int) RETURNS TABLE(k int, sq int) LANGUAGE plxruby AS $$ for i in 1..n k = i sq = i * i emit end return $$; SELECT string_agg(k || ':' || sq, ',') FROM f_table(3); string_agg ------------- 1:1,2:4,3:9 (1 row) -- SELECT INTO with fetch_one! is STRICT (raises when no row is returned) CREATE FUNCTION f_strict() RETURNS int LANGUAGE plxruby AS $$ begin r = fetch_one!("SELECT 1 AS a WHERE false") return r.a rescue => e return -1 end $$; SELECT f_strict() AS strict_no_row; strict_no_row --------------- -1 (1 row) -- backslash literals and interpolation with standard_conforming_strings = off -- are tested in plx_scs_off, which has an alternative output for PostgreSQL 19+ -- interpolation in a SQL argument: a value is bound (or quoted), never spliced -- as SQL text, wherever it sits; ident() quotes identifiers, literal() and raw() -- splice on request CREATE TABLE tpl_t (id int, name text); INSERT INTO tpl_t VALUES (1, 'alice'), (2, 'bob'); CREATE TABLE "tpl Odd" (id int); INSERT INTO "tpl Odd" VALUES (7); CREATE TABLE tpl_log (m text); -- a value round-trips exactly in each position, in a planned statement ($n) -- and in a utility statement (quote_nullable), and nothing it contains runs CREATE FUNCTION tpl_roundtrip(v text) RETURNS text LANGUAGE plxjs AS $$ execute(`DELETE FROM tpl_log`); execute(`INSERT INTO tpl_log VALUES (${v})`); execute(`INSERT INTO tpl_log VALUES ('${v}')`); execute(`INSERT INTO tpl_log VALUES ('<${v}>')`); execute(`COMMENT ON TABLE tpl_log IS '${v}'`); let r = fetch_one(`SELECT count(*) AS c FROM tpl_log WHERE m = $1 OR m = '<' || $1 || '>'`, v); let d = fetch_one(`SELECT obj_description('tpl_log'::regclass) AS d`); return `${r.c}|${d.d = v}`; $$; SELECT v, tpl_roundtrip(v) AS "rows|comment_ok" FROM (VALUES ($x$plain$x$), ($x$it's$x$), ($x$x'); DROP TABLE tpl_t; --$x$), ($x$C:\$x$), ($x$a\'b$x$), ($x$$$; RAISE EXCEPTION 'boom'; $$$x$)) AS p(v); v | rows|comment_ok --------------------------------+----------------- plain | 3|true it's | 3|true x'); DROP TABLE tpl_t; -- | 3|true C:\ | 3|true a\'b | 3|true $$; RAISE EXCEPTION 'boom'; $$ | 3|true (6 rows) SELECT count(*) AS tpl_t_survives FROM tpl_t; tpl_t_survives ---------------- 2 (1 row) -- the generated SQL: values become $n after the call's own binds CREATE FUNCTION tpl_fetch(uname text, lim int) RETURNS text LANGUAGE plxruby AS $$ u = fetch_one("SELECT name FROM tpl_t WHERE name = '#{uname}' LIMIT $1", lim) return u.name $$; SELECT tpl_fetch('bob', 1), tpl_fetch($x$x' OR name = 'bob$x$, 1) IS NULL AS injection_blocked; tpl_fetch | injection_blocked -----------+------------------- bob | t (1 row) SELECT regexp_replace(prosrc, '^.*?BEGIN\n|END;\n/\*plx-orig.*$', '', 'gs') FROM pg_proc WHERE proname = 'tpl_fetch'; regexp_replace ---------------------------------------------------------------------------------------- EXECUTE 'SELECT name FROM tpl_t WHERE name = ($2) LIMIT $1' INTO u USING lim, uname;+ RETURN u.name; + (1 row) -- static SQL: '#{x}' is the variable, not the literal text 'x' CREATE FUNCTION tpl_static(uname text) RETURNS int LANGUAGE plxpython3 AS $$ u = fetch_one(f"SELECT id FROM tpl_t WHERE name = '{uname}'") return u.id $$; SELECT tpl_static('bob'); tpl_static ------------ 2 (1 row) -- ident() quotes an identifier (and makes the call dynamic); PHP spells it -- {ident($t)} CREATE FUNCTION tpl_ident(t text, minid int) RETURNS bigint LANGUAGE plxphp AS $$ $r = fetch_one("SELECT count(*) AS c FROM {ident($t)} WHERE id >= {$minid}"); return $r->c; $$; SELECT tpl_ident('tpl Odd', 7), tpl_ident('tpl_t', 2); tpl_ident | tpl_ident -----------+----------- 1 | 1 (1 row) -- raw() splices deliberately CREATE FUNCTION tpl_raw(ord text) RETURNS text LANGUAGE plxruby AS $$ s = "" query("SELECT name FROM tpl_t ORDER BY #{raw(ord)}").each do |r| s << r.name end return s $$; SELECT tpl_raw('name DESC'); tpl_raw ---------- bobalice (1 row) -- rejected: an interpolation inside a quoted identifier, ident() inside a string DO $d$ BEGIN EXECUTE $q$CREATE FUNCTION tpl_bad1(t text) RETURNS void LANGUAGE plxjs AS $b$ execute(`SELECT * FROM "${t}"`); $b$$q$; EXCEPTION WHEN OTHERS THEN RAISE WARNING 'caught: %', SQLERRM; END $d$; WARNING: caught: plxjs: an interpolation inside a quoted SQL identifier is not allowed; interpolate ident(expr), without the quotes, to quote it DO $d$ BEGIN EXECUTE $q$CREATE FUNCTION tpl_bad2(t text) RETURNS void LANGUAGE plxjs AS $b$ execute(`SELECT '${ident(t)}'`); $b$$q$; EXCEPTION WHEN OTHERS THEN RAISE WARNING 'caught: %', SQLERRM; END $d$; WARNING: caught: plxjs: ident() cannot be used inside a quoted SQL string DO $d$ BEGIN EXECUTE $q$CREATE FUNCTION tpl_bad3(c text) RETURNS void LANGUAGE plxjs AS $b$ execute(`COMMENT ON TABLE tpl_t IS 'x ${c}'`); $b$$q$; EXCEPTION WHEN OTHERS THEN RAISE WARNING 'caught: %', SQLERRM; END $d$; WARNING: caught: plxjs: this statement takes no parameters, so a value inside a quoted SQL string cannot be bound; build the whole value and interpolate it in place of the quoted literal DROP TABLE tpl_t, "tpl Odd", tpl_log; -- a first assignment from an interpolated string is not folded into DECLARE: -- the initializer would run when the function starts and read the old value CREATE FUNCTION f_fold_js() RETURNS text LANGUAGE plxjs AS $$ let v = "v0"; v = "v2"; let s = `got ${v}`; return s; $$; CREATE FUNCTION f_fold_py() RETURNS text LANGUAGE plxpython3 AS $$ v = "v0" v = "v2" s = f"got {v}" return s $$; CREATE FUNCTION f_fold_rb() RETURNS text LANGUAGE plxruby AS $$ v = "v0" v = "v2" s = "got #{v}" return s $$; CREATE FUNCTION f_fold_php() RETURNS text LANGUAGE plxphp AS $$ $v = "v0"; $v = "v2"; $s = "got $v"; return $s; $$; SELECT f_fold_js(), f_fold_py(), f_fold_rb(), f_fold_php(); f_fold_js | f_fold_py | f_fold_rb | f_fold_php -----------+-----------+-----------+------------ got v2 | got v2 | got v2 | got v2 (1 row) -- a plain literal is still folded; a constant may use parameters and earlier -- constants CREATE FUNCTION f_fold_const(n int) RETURNS text LANGUAGE plxruby AS $$ k = 10 #:: int const c = "n=#{n} k=#{k}" #:: text const s = "plain" return "#{c} #{s}" $$; SELECT f_fold_const(5); f_fold_const ---------------- n=5 k=10 plain (1 row) SELECT regexp_replace(prosrc, '/\*plx-orig.*', '') FROM pg_proc WHERE proname = 'f_fold_const'; regexp_replace --------------------------------------------------------------- /*plx:v1:plxruby:784cd38a*/ + DECLARE + k CONSTANT int := 10; + c CONSTANT text := 'n=' || (n)::text || ' k=' || (k)::text;+ s text := 'plain'; + BEGIN + RETURN (c)::text || ' ' || (s)::text; + END; + (1 row) DROP FUNCTION f_fold_js(), f_fold_py(), f_fold_rb(), f_fold_php(), f_fold_const(int); -- .to_s / .to_i / .to_f are rewritten as whole words only, so a field whose -- name starts with to_s, to_i or to_f is left alone; x.to_s() also works CREATE FUNCTION f_to_cast(n int) RETURNS text LANGUAGE plxruby AS $$ r = fetch_one("select 'shipped' as to_status, 'x' as to_s_name, 2 as to_items") return "#{r.to_status}/#{r.to_s_name}/#{r.to_items}/#{n.to_s}/#{n.to_s()}/#{'4.5'.to_f}/#{'12'.to_i + 1}" $$; SELECT f_to_cast(3); f_to_cast ------------------------ shipped/x/2/3/3/4.5/13 (1 row) DROP FUNCTION f_to_cast(int);