-- Regression tests for the SQL template scanner: how an interpolation in the -- SQL argument of execute, fetch_one, query and friends is lowered. -- Each case names its PR 24 review finding ([n]). CREATE EXTENSION IF NOT EXISTS plx; NOTICE: extension "plx" already exists, skipping SET client_min_messages = warning; -- fs_err(q) runs q and returns 'SQLSTATE: message' if it fails. The message -- leaves out the error position, which differs between PostgreSQL 13 -- (plpgsql prefixes each expression with SELECT) and later versions. CREATE FUNCTION fs_err(q text) RETURNS text LANGUAGE plpgsql AS $h$ BEGIN EXECUTE q; RETURN 'no error'; EXCEPTION WHEN OTHERS THEN RETURN SQLSTATE || ': ' || SQLERRM; END $h$; CREATE TABLE fs_victim (id int); -- [2]: an apostrophe inside a SQL comment does not open a string, so the -- quoted literal after it is still seen as quoted and the value stays data CREATE FUNCTION fs_c_block(c text) RETURNS text LANGUAGE plxruby AS $$ execute("COMMENT /* the owner's note */ ON TABLE fs_victim IS '#{c}'") d = fetch_one("SELECT obj_description('fs_victim'::regclass) AS d") return d.d $$; SELECT fs_c_block('hello'); fs_c_block ------------ hello (1 row) SELECT fs_c_block('; DROP TABLE fs_victim; --'); fs_c_block ---------------------------- ; DROP TABLE fs_victim; -- (1 row) SELECT to_regclass('fs_victim') IS NOT NULL AS victim_survives; victim_survives ----------------- t (1 row) CREATE FUNCTION fs_c_line(c text) RETURNS text LANGUAGE plxruby AS $$ execute("COMMENT -- the owner's note\nON TABLE fs_victim IS '#{c}'") d = fetch_one("SELECT obj_description('fs_victim'::regclass) AS d") return d.d $$; SELECT fs_c_line('; DROP TABLE fs_victim; --'); fs_c_line ---------------------------- ; DROP TABLE fs_victim; -- (1 row) SELECT to_regclass('fs_victim') IS NOT NULL AS victim_survives; victim_survives ----------------- t (1 row) -- [2]: block comments nest CREATE FUNCTION fs_c_nested(c text) RETURNS text LANGUAGE plxjs AS $$ execute(`COMMENT /* outer /* it's */ still's */ ON TABLE fs_victim IS '${c}'`); let d = fetch_one(`SELECT obj_description('fs_victim'::regclass) AS d`); return d.d; $$; SELECT fs_c_nested('; DROP TABLE fs_victim; --'); fs_c_nested ---------------------------- ; DROP TABLE fs_victim; -- (1 row) SELECT to_regclass('fs_victim') IS NOT NULL AS victim_survives; victim_survives ----------------- t (1 row) -- [2]: static SQL reads the value, not the variable's name CREATE FUNCTION fs_c_static(x text) RETURNS text LANGUAGE plxjs AS $$ let r = fetch_one(`SELECT /* the user's tag */ '${x}' AS v`); return r.v; $$; SELECT fs_c_static('hello'); fs_c_static ------------- hello (1 row) CREATE FUNCTION fs_c_static_line(x text) RETURNS text LANGUAGE plxjs AS $$ let r = fetch_one(`SELECT 'a' -- it's || '${x}' AS v`); return r.v; $$; SELECT fs_c_static_line('hello'); fs_c_static_line ------------------ ahello (1 row) -- [2]: an interpolation inside a SQL comment is rejected when the function is -- created: it would silently do nothing CREATE FUNCTION fs_c_in_block(x text) RETURNS text LANGUAGE plxjs AS $$ let r = fetch_one(`SELECT 1 AS v /* ${x} */`); return r.v; $$; ERROR: plxjs: an interpolation inside a SQL comment is not allowed; move it out of the comment DETAIL: at plxjs source line 2 CREATE FUNCTION fs_c_in_line(c text) RETURNS void LANGUAGE plxruby AS $$ execute("COMMENT ON TABLE fs_victim IS 'x' -- #{c}\n") $$; ERROR: plxruby: an interpolation inside a SQL comment is not allowed; move it out of the comment DETAIL: at plxruby source line 2 -- [4]: a dollar-quoted string is skipped as a whole, so quotes inside it do -- not count, and a $ inside a name or a $n parameter does not start one CREATE FUNCTION fs_d_skip(x text) RETURNS text LANGUAGE plxjs AS $f$ let r = fetch_one(`SELECT $q$it's$q$ || '${x}' AS v`); return r.v; $f$; SELECT fs_d_skip('hello'); fs_d_skip ----------- it'shello (1 row) CREATE FUNCTION fs_d_skip_dyn(x text) RETURNS text LANGUAGE plxjs AS $f$ let r = fetch_one(`SELECT $$it's$$ || '${x}' || $1 AS v`, '!'); return r.v; $f$; SELECT fs_d_skip_dyn('hello'); fs_d_skip_dyn --------------- it'shello! (1 row) CREATE FUNCTION fs_d_name(x text) RETURNS text LANGUAGE plxjs AS $f$ let r = fetch_one(`SELECT v FROM (SELECT 1 AS a$q$, '${x}' AS v) AS s`); return r.v; $f$; SELECT fs_d_name('hello'); fs_d_name ----------- hello (1 row) -- [4]: an interpolation inside a dollar-quoted string is rejected: a $tag$ in -- the value would end the string, and the value would run as SQL CREATE FUNCTION fs_d_dyn(t text, c text) RETURNS void LANGUAGE plxjs AS $f$ execute(`COMMENT ON TABLE ${ident(t)} IS $c$${c}$c$`); $f$; ERROR: plxjs: an interpolation inside a dollar-quoted SQL string is not allowed; use a quoted literal '...' instead DETAIL: at plxjs source line 2 CREATE FUNCTION fs_d_dyn_rb(t text, c text) RETURNS void LANGUAGE plxruby AS $f$ execute("COMMENT ON TABLE #{ident(t)} IS $c$ #{c} $c$") $f$; ERROR: plxruby: an interpolation inside a dollar-quoted SQL string is not allowed; use a quoted literal '...' instead DETAIL: at plxruby source line 2 CREATE FUNCTION fs_d_static(s text) RETURNS text LANGUAGE plxjs AS $f$ let r = fetch_one(`SELECT $q$ ${s} $q$ AS c`); return r.c; $f$; ERROR: plxjs: an interpolation inside a dollar-quoted SQL string is not allowed; use a quoted literal '...' instead DETAIL: at plxjs source line 2 CREATE FUNCTION fs_d_py(x text) RETURNS void LANGUAGE plxpython3 AS $f$ execute(f"SELECT $q$ {x} $q$ AS v") $f$; ERROR: plxpython3: an interpolation inside a dollar-quoted SQL string is not allowed; use a quoted literal '...' instead DETAIL: at plxpython3 source line 2 -- [4]: E'' strings with backslash escapes, U&'' strings, doubled quotes and -- quoted identifiers are each read as one token CREATE FUNCTION fs_l_forms(x text) RETURNS text LANGUAGE plxruby AS $f$ r = fetch_one("SELECT E'it\\'s' || U&'d\\0061t\\+000061' || 'it''s' || '#{x}' AS \"it's\"") return r["it's"] $f$; SELECT fs_l_forms('hello'); fs_l_forms ------------------- it'sdatait'shello (1 row) -- [4]: an interpolation inside a U&'', B'', X'', N'' or typed literal is -- rejected: the value cannot be spliced into such a literal CREATE FUNCTION fs_l_uesc(x text) RETURNS text LANGUAGE plxruby AS $f$ r = fetch_one("SELECT U&'\\0041#{x}' AS v") return r.v $f$; ERROR: plxruby: an interpolation inside a prefixed SQL string (U&'...', B'...', X'...', N'...' or type'...') is not allowed; interpolate the whole value in place of the literal DETAIL: at plxruby source line 2 CREATE FUNCTION fs_l_nchar(x text) RETURNS text LANGUAGE plxjs AS $f$ let r = fetch_one(`SELECT N'${x}' AS v`); return r.v; $f$; ERROR: plxjs: an interpolation inside a prefixed SQL string (U&'...', B'...', X'...', N'...' or type'...') is not allowed; interpolate the whole value in place of the literal DETAIL: at plxjs source line 2 CREATE FUNCTION fs_l_typed(x text) RETURNS text LANGUAGE plxjs AS $f$ let r = fetch_one(`SELECT text'a${x}' AS v`); return r.v; $f$; ERROR: plxjs: an interpolation inside a prefixed SQL string (U&'...', B'...', X'...', N'...' or type'...') is not allowed; interpolate the whole value in place of the literal DETAIL: at plxjs source line 2 -- [4]: an interpolation joined to a SQL name, number or $ sign is rejected: -- it would change the name ($2 in v$2 is part of the name) or form a $tag$ CREATE FUNCTION fs_a_name(x text) RETURNS text LANGUAGE plxjs AS $f$ let r = fetch_one(`SELECT count${x} AS v`); return r.v; $f$; ERROR: plxjs: an interpolation cannot be joined to a SQL name, number or $ sign; separate them with a space DETAIL: at plxjs source line 2 CREATE FUNCTION fs_a_after(x text) RETURNS text LANGUAGE plxruby AS $f$ r = fetch_one("SELECT #{x}abc FROM fs_victim WHERE 1 = $1", 1) return r.abc $f$; ERROR: plxruby: an interpolation cannot be joined to a SQL name, number or $ sign; separate them with a space DETAIL: at plxruby source line 2 CREATE FUNCTION fs_a_dollar(t text) RETURNS void LANGUAGE plxjs AS $f$ execute(`SELECT $${ident(t)}$ AS v`); $f$; ERROR: plxjs: an interpolation cannot be joined to a SQL name, number or $ sign; separate them with a space DETAIL: at plxjs source line 2 -- [4]: with standard_conforming_strings off (PostgreSQL 13 to 18), a literal -- in dynamic SQL that ends in a backslash still ends there, so the value -- after it stays data. fs_scs_off() turns the setting off where the server -- allows it; 19 and later reject the setting and the cases run with it on. CREATE PROCEDURE fs_scs_off() LANGUAGE plpgsql AS $h$ BEGIN PERFORM set_config('standard_conforming_strings', 'off', false); EXCEPTION WHEN OTHERS THEN NULL; END $h$; CREATE FUNCTION fs_scs_note(c text) RETURNS text LANGUAGE plxruby AS $f$ execute("COMMENT ON TABLE fs_victim IS 'C:\\'\n'#{c}'") d = fetch_one("SELECT obj_description('fs_victim'::regclass) AS d") return d.d $f$; CREATE FUNCTION fs_scs_bound(x text) RETURNS text LANGUAGE plxruby AS $f$ r = fetch_one("SELECT 'C:\\' || '#{x}' || $1 AS v", '!') return r.v $f$; CALL fs_scs_off(); SELECT fs_scs_note('; DROP TABLE fs_victim; --'); fs_scs_note ------------------------------- C:\; DROP TABLE fs_victim; -- (1 row) SELECT fs_scs_bound('hello'); fs_scs_bound -------------- C:\hello! (1 row) RESET standard_conforming_strings; SELECT to_regclass('fs_victim') IS NOT NULL AS victim_survives; victim_survives ----------------- t (1 row) SELECT fs_scs_note('hello'); fs_scs_note ------------- C:\hello (1 row) -- [4]: a -- comment ends at a carriage return as well as at a newline CREATE FUNCTION fs_c_cr(c text) RETURNS text LANGUAGE plxruby AS $f$ execute("COMMENT ON TABLE fs_victim IS -- note\r'#{c}'") d = fetch_one("SELECT obj_description('fs_victim'::regclass) AS d") return d.d $f$; SELECT fs_c_cr('after a carriage return'); fs_c_cr ------------------------- after a carriage return (1 row) -- [3]: typed literals (text'...') and N'...' literals are ordinary strings to -- PostgreSQL, so they too are written as E'...' when they hold a backslash. -- With standard_conforming_strings off, the backslash must not swallow the -- closing quote and run the text after it as SQL. CREATE FUNCTION fs_t_typed() RETURNS text LANGUAGE plxplsql AS $f$ BEGIN RETURN text'C:\' || ' || 42::text; --'; END; $f$; CREATE FUNCTION fs_t_nchar() RETURNS text LANGUAGE plxplsql AS $f$ BEGIN RETURN N'C:\' || ' || 42::text; --'; END; $f$; CREATE FUNCTION fs_t_varchar() RETURNS text LANGUAGE plxplsql AS $f$ BEGIN RETURN varchar'C:\' || ' || 42::text; --'; END; $f$; CREATE FUNCTION fs_t_static() RETURNS text LANGUAGE plxjs AS $f$ let r = fetch_one(`SELECT text'C:\\' || ' || 42::text; --' AS v`); return r.v; $f$; CREATE FUNCTION fs_t_dyn() RETURNS text LANGUAGE plxjs AS $f$ let r = fetch_one(`SELECT text'C:\\' || $1 AS v`, '!'); return r.v; $f$; -- plpgsql prepares a statement at its first run, so the first calls are -- the ones made with the setting off CALL fs_scs_off(); SELECT fs_t_typed() = E'C:\\\n|| 42::text; --' AS typed_ok, fs_t_nchar() = E'C:\\\n|| 42::text; --' AS nchar_ok, fs_t_varchar() = E'C:\\\n|| 42::text; --' AS varchar_ok, fs_t_static() = E'C:\\\n|| 42::text; --' AS static_ok; typed_ok | nchar_ok | varchar_ok | static_ok ----------+----------+------------+----------- t | t | t | t (1 row) SELECT fs_t_dyn(); fs_t_dyn ---------- C:\! (1 row) RESET standard_conforming_strings; SELECT fs_t_typed() = E'C:\\\n|| 42::text; --' AS typed_ok, fs_t_nchar() = E'C:\\\n|| 42::text; --' AS nchar_ok, fs_t_varchar() = E'C:\\\n|| 42::text; --' AS varchar_ok, fs_t_static() = E'C:\\\n|| 42::text; --' AS static_ok; typed_ok | nchar_ok | varchar_ok | static_ok ----------+----------+------------+----------- t | t | t | t (1 row) SELECT fs_t_dyn(); fs_t_dyn ---------- C:\! (1 row) -- [3]: a -- comment ends at a carriage return, so a literal after one is -- rewritten too CREATE FUNCTION fs_t_cr() RETURNS text LANGUAGE plxplsql AS E'BEGIN\n -- note\r RETURN ''C:\\'' || ''\n|| 42::text; --'';\nEND;\n'; CALL fs_scs_off(); SELECT fs_t_cr() = E'C:\\\n|| 42::text; --' AS cr_ok; cr_ok ------- t (1 row) RESET standard_conforming_strings; SELECT fs_t_cr() = E'C:\\\n|| 42::text; --' AS cr_ok; cr_ok ------- t (1 row) -- [22]: in static SQL, a value interpolated where SQL needs a name fails -- when the function is created. It used to become the bare variable name, -- which plpgsql does not replace there, so FROM #{fs_tab} read the table -- named fs_tab whatever the variable held. CREATE TABLE fs_tab (x int); INSERT INTO fs_tab VALUES (1), (2), (3); CREATE TABLE fs_users (id int); INSERT INTO fs_users VALUES (1); SELECT fs_err($q$CREATE FUNCTION fs_i_from(fs_tab text) RETURNS bigint LANGUAGE plxruby AS $f$ u = fetch_one("SELECT count(*) AS c FROM #{fs_tab}") return u.c $f$$q$); fs_err ------------------------------------ 42601: syntax error at or near ")" (1 row) SELECT fs_err($q$CREATE FUNCTION fs_i_perform(fs_tab text) RETURNS void LANGUAGE plxjs AS $f$ perform(`DELETE FROM ${fs_tab}`); $f$$q$); fs_err ------------------------------------ 42601: syntax error at or near "(" (1 row) SELECT fs_err($q$CREATE FUNCTION fs_i_query(fs_tab text) RETURNS SETOF int LANGUAGE plxjs AS $f$ return_query(`SELECT x FROM ${fs_tab}`); $f$$q$); fs_err ------------------------------------ 42601: syntax error at or near ")" (1 row) SELECT fs_err($q$CREATE FUNCTION fs_i_alias(a text) RETURNS int LANGUAGE plxjs AS $f$ let r = fetch_one(`SELECT 1 AS ${a}`); return 1; $f$$q$); fs_err ------------------------------------ 42601: syntax error at or near "(" (1 row) SELECT count(*) AS fs_tab_rows FROM fs_tab; fs_tab_rows ------------- 3 (1 row) -- [22]: a value where SQL takes an expression still works, after FROM too CREATE FUNCTION fs_i_value(n int, s text) RETURNS text LANGUAGE plxjs AS $f$ let r = fetch_one(`SELECT substring(${s} FROM ${n}) || count(*) AS v FROM fs_tab WHERE x >= ${n} LIMIT ${n}`); return r.v; $f$; SELECT fs_i_value(2, 'abcdef'); fs_i_value ------------ bcdef2 (1 row) -- [review of PR 24 fixes]: an E'...' string whose text ends in an unpaired -- backslash right before an interpolation is rejected. PostgreSQL would read -- the quote plx writes after the backslash as an escaped quote, so plx and -- PostgreSQL would disagree about where the string ends, and a later -- literal() or ident() value could run as SQL. CREATE TABLE fs_secret (s text); INSERT INTO fs_secret VALUES ('TOPSECRET'); CREATE FUNCTION fs_e_lit(x text, y text) RETURNS text LANGUAGE plxruby AS $f$ r = fetch_one("SELECT E'a\\#{x}' AS v, #{literal(y)} AS w") return r.v $f$; ERROR: plxruby: an interpolation cannot follow an unpaired backslash in an E'...' SQL string; write a literal backslash as two backslashes in the SQL text (four inside a double-quoted, template or f-string) DETAIL: at plxruby source line 2 CREATE FUNCTION fs_e_ident(x text, y text) RETURNS text LANGUAGE plxruby AS $f$ r = fetch_one("SELECT E'a\\#{x}' AS v, 1 AS #{ident(y)}") return r.v $f$; ERROR: plxruby: an interpolation cannot follow an unpaired backslash in an E'...' SQL string; write a literal backslash as two backslashes in the SQL text (four inside a double-quoted, template or f-string) DETAIL: at plxruby source line 2 CREATE FUNCTION fs_e_js(x text, y text) RETURNS text LANGUAGE plxjs AS $f$ let r = fetch_one(`SELECT E'a\\${x}' AS v, ${literal(y)} AS w`); return r.v; $f$; ERROR: plxjs: an interpolation cannot follow an unpaired backslash in an E'...' SQL string; write a literal backslash as two backslashes in the SQL text (four inside a double-quoted, template or f-string) DETAIL: at plxjs source line 2 CREATE FUNCTION fs_e_bind(x text, y text) RETURNS text LANGUAGE plxjs AS $f$ let r = fetch_one(`SELECT E'a\\${x}' AS v, $1 AS w`, y); return r.v; $f$; ERROR: plxjs: an interpolation cannot follow an unpaired backslash in an E'...' SQL string; write a literal backslash as two backslashes in the SQL text (four inside a double-quoted, template or f-string) DETAIL: at plxjs source line 2 CREATE FUNCTION fs_e_static(x text) RETURNS text LANGUAGE plxruby AS $f$ r = fetch_one("SELECT E'a\\#{x}' AS v") return r.v $f$; ERROR: plxruby: an interpolation cannot follow an unpaired backslash in an E'...' SQL string; write a literal backslash as two backslashes in the SQL text (four inside a double-quoted, template or f-string) DETAIL: at plxruby source line 2 -- a doubled backslash is one literal backslash, and the value follows it CREATE FUNCTION fs_e_ok(x text, y text) RETURNS text LANGUAGE plxruby AS $f$ r = fetch_one("SELECT E'a\\\\#{x}' || #{literal(y)} AS v") return r.v $f$; SELECT fs_e_ok('b', ') || (SELECT s FROM fs_secret) || ('); fs_e_ok ---------------------------------------- a\b) || (SELECT s FROM fs_secret) || ( (1 row) CREATE FUNCTION fs_e_ok_static(x text) RETURNS text LANGUAGE plxruby AS $f$ r = fetch_one("SELECT E'a\\\\#{x}' AS v") return r.v $f$; SELECT fs_e_ok_static('b'); fs_e_ok_static ---------------- a\b (1 row) -- [review of PR 24 fixes]: in static SQL an interpolated expression is one -- value, as it is in dynamic SQL. 2.0.1 wrote it without parentheses, so -- the SQL around it could split it (a + b * 10). CREATE FUNCTION fs_p_js(a int, b int) RETURNS int LANGUAGE plxjs AS $f$ let r = fetch_one(`SELECT ${a + b} * 10 AS v`); return r.v; $f$; SELECT fs_p_js(1, 2); fs_p_js --------- 30 (1 row) CREATE FUNCTION fs_p_py(a boolean, b boolean) RETURNS boolean LANGUAGE plxpython3 AS $f$ r = fetch_one(f"SELECT {a or b} AND false AS v") return r.v $f$; SELECT fs_p_py(true, false); fs_p_py --------- f (1 row) -- R2-C11 (PR 24 review r2 c1[1]): a typed literal with an interpolation, -- TYPE '...#{x}...', becomes CAST(('...' || x || '...') AS TYPE). Writing -- TYPE ('...' || x) would be a syntax error (interval (...) is a typmod). SET datestyle = 'ISO, MDY'; SET intervalstyle = postgres; CREATE FUNCTION fs_r2c11_js(d int, k int) RETURNS text LANGUAGE plxjs AS $$ let r = fetch_one(`SELECT (interval '${d} days')::text AS v LIMIT $1`, k); return r.v; $$; SELECT fs_r2c11_js(5, 1) AS r; -- 5 days r -------- 5 days (1 row) CREATE FUNCTION fs_r2c11_py(d int) RETURNS text LANGUAGE plxpython3 AS $$ r = fetch_one(f"SELECT (INTERVAL '{d} days')::text || ' ' || date '2020-01-{d}' || ' ' || (double precision '1.{d}' * 2) || ' ' || (timestamp with time zone '2020-01-0{d} 00:00 UTC' AT TIME ZONE 'UTC') AS v") return r.v $$; SELECT fs_r2c11_py(5) AS r; -- 5 days 2020-01-05 3 2020-01-05 00:00:00 r ----------------------------------------- 5 days 2020-01-05 3 2020-01-05 00:00:00 (1 row) RESET datestyle; RESET intervalstyle; -- a word that is not a type name leaves the literal as it was CREATE FUNCTION fs_r2c11_like(p text) RETURNS boolean LANGUAGE plxruby AS $$ r = fetch_one("SELECT 'abc' LIKE 'a#{p}%' AS v") return r.v $$; SELECT fs_r2c11_like('b') AS r; -- t r --- t (1 row) -- a whole quoted literal has the type of the value: compare a text value with -- a date through an explicit cast CREATE TABLE fs_r2c11_t (id int, ts date); INSERT INTO fs_r2c11_t VALUES (1, '2020-01-05'), (2, '2021-01-01'); CREATE FUNCTION fs_r2c11_cast(since text, k int) RETURNS text LANGUAGE plxruby AS $$ r = fetch_one("SELECT count(*) AS c FROM fs_r2c11_t WHERE ts > '#{since}'::date LIMIT $1", k) return "#{r.c}" $$; SELECT fs_r2c11_cast('2020-06-01', 1) AS r; -- 1 r --- 1 (1 row) CREATE FUNCTION fs_r2c11_nocast(since text, k int) RETURNS text LANGUAGE plxruby AS $$ r = fetch_one("SELECT count(*) AS c FROM fs_r2c11_t WHERE ts > '#{since}' LIMIT $1", k) return "#{r.c}" $$; SELECT fs_err($q$SELECT fs_r2c11_nocast('2020-06-01', 1)$q$) AS r; -- 42883: operator does not exist: date > text r --------------------------------------------- 42883: operator does not exist: date > text (1 row) -- R2-T10 (PR 24 review r2 t1[0]): one name position is not caught at CREATE -- FUNCTION. In static SQL, UPDATE ... SET #{x} = v becomes SET (x) = v, -- which PostgreSQL reads as a one-column list. With a plain value the -- statement fails when it runs; with a sub-SELECT or ROW() it updates the -- column named x whatever the variable holds. doc/LIMITATIONS.md and -- DYNAMIC-SQL.md say so; the cases pin the behaviour so that a change is -- noticed. ident() is the fix. CREATE TABLE fs_r2t10 (id int, balance int, x int); INSERT INTO fs_r2t10 VALUES (1, 100, 7); CREATE FUNCTION fs_r2t10_val(x text) RETURNS void LANGUAGE plxjs AS $$ perform(`UPDATE fs_r2t10 SET ${x} = 0 WHERE id = 1`); $$; SELECT fs_err($q$SELECT fs_r2t10_val('balance')$q$) AS r; r ------------------------------------------------------------------------------------------ 0A000: source for a multiple-column UPDATE item must be a sub-SELECT or ROW() expression (1 row) CREATE FUNCTION fs_r2t10_sub(x text) RETURNS void LANGUAGE plxjs AS $$ perform(`UPDATE fs_r2t10 SET ${x} = (SELECT 1) WHERE id = 1`); $$; SELECT fs_r2t10_sub('balance'); fs_r2t10_sub -------------- (1 row) SELECT balance, x FROM fs_r2t10; -- pinned: column x changes to 1, balance stays 100 balance | x ---------+--- 100 | 1 (1 row) CREATE FUNCTION fs_r2t10_ident(x text) RETURNS void LANGUAGE plxjs AS $$ perform(`UPDATE fs_r2t10 SET ${ident(x)} = 0 WHERE id = 1`); $$; SELECT fs_r2t10_ident('balance'); fs_r2t10_ident ---------------- (1 row) SELECT balance, x FROM fs_r2t10; -- 0 1 balance | x ---------+--- 0 | 1 (1 row) -- R3-T20 (PR 24 review r3): the CAST lowering of a typed literal missed -- three forms. A literal that is only the interpolation, TYPE '#{x}', -- became TYPE (x), a syntax error for interval, int or timestamp. It is now -- CAST((x)::text AS TYPE). char '...' became CAST(... AS char), which is -- char(1) and cut the value to one character; a char literal is unbounded -- bpchar. An interval field after the literal, interval '#{n}' hour, has no -- CAST equivalent (PostgreSQL applies the field only to a bare literal's -- input), so it is rejected. SET datestyle = 'ISO, MDY'; SET intervalstyle = postgres; CREATE FUNCTION fs_r3t20_rb(d text, a int, ts text) RETURNS text LANGUAGE plxruby AS $$ r = fetch_one("SELECT (interval '#{d}')::text || ' ' || (int '#{a}' + 1) || ' ' || timestamp '#{ts}' || ' ' || char 'ab#{a}' || '|' || character 'x#{a}yz' || '|' || char '#{a}' AS v") return r.v $$; SELECT fs_r3t20_rb('5 days', 41, '2020-01-02 03:04:05') AS r; r --------------------------------------------- 5 days 42 2020-01-02 03:04:05 ab41|x41yz|41 (1 row) -- 5 days 42 2020-01-02 03:04:05 ab41|x41yz|41 CREATE FUNCTION fs_r3t20_js(d text, a int, ts text, k int) RETURNS text LANGUAGE plxjs AS $$ let r = fetch_one(`SELECT (interval '${d}')::text || ' ' || (int '${a}' + 1) || ' ' || timestamp '${ts}' || ' ' || char 'ab${a}' || '|' || char '${a}' AS v LIMIT $1`, k); return r.v; $$; SELECT fs_r3t20_js('5 days', 41, '2020-01-02 03:04:05', 1) AS r; r --------------------------------------- 5 days 42 2020-01-02 03:04:05 ab41|41 (1 row) -- 5 days 42 2020-01-02 03:04:05 ab41|41 CREATE FUNCTION fs_r3t20_field(a int) RETURNS text LANGUAGE plxruby AS $$ r = fetch_one("SELECT (interval '#{a}' hour)::text AS v") return r.v $$; ERROR: an interval field after an interpolated literal, interval '...' hour, is not supported HINT: Put the unit inside the literal, after the interpolated value, or call make_interval(). CREATE FUNCTION fs_r3t20_field2(a int, k int) RETURNS text LANGUAGE plxjs AS $$ let r = fetch_one(`SELECT (interval '${a} days' DAY)::text AS v LIMIT $1`, k); return r.v; $$; ERROR: an interval field after an interpolated literal, interval '...' day, is not supported HINT: Put the unit inside the literal, after the interpolated value, or call make_interval(). -- the unit inside the literal works CREATE FUNCTION fs_r3t20_unit(a int) RETURNS text LANGUAGE plxruby AS $$ r = fetch_one("SELECT (interval '#{a} hours')::text AS v") return r.v $$; SELECT fs_r3t20_unit(41) AS r; -- 41:00:00 r ---------- 41:00:00 (1 row) RESET datestyle; RESET intervalstyle;