Contents
Interpolation in SQL
Every dialect with string interpolation (plxruby #{x}, plxphp {$x}/$x,
plxjs and plxts ${x}, plxpython3 f-string {x}) can interpolate values into
the SQL argument of query, fetch_one, fetch_one!, execute,
open_cursor, return_query and perform. Since 3.0, an interpolated value
never becomes SQL text. It is bound as a parameter, or quoted as a literal, and
the author asks explicitly for anything else.
Values
u = fetch_one("SELECT id, name FROM users WHERE name = '#{uname}' LIMIT $1", lim)
becomes
EXECUTE 'SELECT id, name FROM users WHERE name = ($2) LIMIT $1' INTO u USING lim, uname;
| Where the interpolation sits | Static SQL | Dynamic SQL |
|---|---|---|
outside quotes: id = #{n} |
id = (n) (plpgsql binds the variable) |
id = $k, passed with USING |
where SQL needs a name: FROM #{t} |
FROM (t): CREATE FUNCTION fails with a syntax error (not in UPDATE ... SET #{c} =; see below) |
FROM $k: the statement fails when it runs |
a whole quoted literal: '#{s}' |
(s) |
($k) |
inside a longer literal: '%#{s}%' |
('%' || (s)::text || '%') |
('%' || ($k)::text || '%') |
a whole literal after a type name: interval '#{s}' |
CAST((s)::text AS interval) |
CAST(($k)::text AS interval) |
inside a literal after a type name: interval '#{n} days' |
CAST(('' || (n)::text || ' days') AS interval) |
CAST(('' || ($k)::text || ' days') AS interval) |
before an interval field: interval '#{n}' hour |
rejected | rejected |
inside a quoted identifier: "#{t}" |
rejected | rejected |
inside a SQL comment: -- #{x}, /* #{x} */ |
rejected | rejected |
inside a dollar-quoted string: $q$#{s}$q$ |
rejected | rejected |
inside a prefixed literal: U&'#{s}', N'#{s}', text'#{s}' |
rejected | rejected |
joined to a name, number or $: v#{x}, #{x}abc |
rejected | rejected |
right after an unpaired backslash in an E'...' string: E'a\\#{x}' in a Ruby string |
rejected | rejected |
plx reads the SQL string as PostgreSQL does. A quote inside a -- or
/* */ comment (block comments nest) or inside a dollar-quoted string does
not open a string, and E'...' strings, U&'...' strings, doubled quotes
('it''s') and quoted identifiers are each read as one token. Each rejected
form fails when the function is created. In dynamic SQL, plx writes a literal
that contains a backslash as E'...', as it does for the function body, so
the SQL reads the same whatever standard_conforming_strings the caller uses.
To end the text of an E'...' string with a backslash right before an
interpolation, write the backslash twice in the SQL text. The dialect’s own
string escapes come first: in a double-quoted string (plxruby, plxphp), a
template string (plxjs, plxts) or an f-string (plxpython3), \\ is one
backslash, so the SQL text E'a\\#{x}' is written "SELECT E'a\\\\#{x}'" in plxruby. Written
with two backslashes in the source, the SQL text has one, and plx rejects it.
With one backslash in the source, \#{x} is an escaped interpolation: the
SQL gets the text #{x}.
A whole quoted literal, '#{s}', becomes the value itself, with the value’s
type. Where SQL needs another type, cast it: with a text variable since,
ts > '#{since}' fails with operator does not exist: date > text, and
ts > '#{since}'::date works. A type name before a literal that holds an
interpolation makes plx write a CAST to that type. plx knows the built-in
type names, including double precision, character varying and
timestamp with time zone; after any other word the literal stays text. A
char or character literal becomes a CAST to bpchar, so the value
keeps its length, as char 'abc' does. A type with a modifier, such as
varchar(2) '#{s}', is not recognised and fails with a syntax error; write
CAST('#{s}' AS varchar(2)). An interval field after the literal,
interval '#{n}' hour, is rejected, because a CAST cannot give it the
literal’s meaning: write the unit inside the literal, interval '#{n} hours',
or call make_interval(hours => #{n}).
A call is static when its SQL argument is a single string literal and it has
no bind arguments; otherwise it runs as dynamic SQL (EXECUTE). execute is
always dynamic. Interpolated values are numbered after the call’s own bind
arguments, so $1 in the example above is still lim.
'#{s}' used to put the text of the expression into the SQL (a string literal
reading uname), and in a dynamic call a value was concatenated into the SQL
unquoted. Both are gone: a value is data wherever it appears.
Statements that take no parameters
PostgreSQL accepts $n parameters only in planned statements: SELECT,
INSERT, UPDATE, DELETE, MERGE, WITH, VALUES, TABLE, CALL and
EXPLAIN. In any other dynamic statement (CREATE, ALTER, SET, COMMENT,
GRANT, and so on), a value is spliced through quote_nullable() instead:
execute("COMMENT ON TABLE #{ident(t)} IS '#{c}'")
execute("SET application_name = #{appname}")
EXECUTE 'COMMENT ON TABLE ' || quote_ident((t)::text) || ' IS ' || quote_nullable((c)::text);
EXECUTE 'SET application_name = ' || quote_nullable((appname)::text);
A value inside a longer quoted literal (IS 'note: #{c}') cannot be quoted in
such a statement and is rejected; build the whole value first and interpolate
it in place of the literal.
Identifiers and SQL fragments
Three helpers, recognized only as the whole of an interpolation in a SQL
argument (plxphp spells them {ident($t)}, {literal($v)}, {raw($s)}), make
the call dynamic and splice their result into the SQL text:
| Helper | Lowers to | Use for |
|---|---|---|
ident(e) |
quote_ident(e) |
a table, column or other identifier |
literal(e) |
quote_nullable(e) |
a value as a quoted literal, in any statement |
raw(e) |
e as text, unquoted |
a trusted SQL fragment (for example a sort order) |
let r = fetch_one(`SELECT count(*) AS c FROM ${ident(tbl)} WHERE id >= ${minid}`);
for (const row of query(`SELECT name FROM users ORDER BY ${raw(order)}`)) { ... }
ident() and literal() cannot appear inside quotes; raw() is the only way
to put caller-supplied text into SQL unquoted, and it is the author’s
responsibility to make that text safe.
Escaped interpolations
A backslash before an interpolation makes it plain text, as it does in a
string value: plxphp \$x and {\$x}, plxjs and plxts \${x}, plxruby
\#{x}. So fetch_one("SELECT '\$x' AS v") in plxphp returns the text $x.
In a plxpython3 f-string, \{x} keeps the backslash and still interpolates,
as in Python; write {{x}} for the text {x}.
In plxphp, \{ is no escape, as in PHP: "\{$x}" keeps the backslash and
the braces and interpolates $x, giving \{5} when $x is 5.
What is not covered
Only interpolation written directly in the SQL argument is seen. A string assembled earlier and passed in a variable is ordinary string concatenation:
let sql = `SELECT * FROM t WHERE name = '${name}'`; // concatenation
execute(sql); // runs whatever sql holds
Write the interpolation in the call, or use bind arguments
(execute("SELECT * FROM t WHERE name = $1", name)).
Upgrading from 2.x
Functions already in the catalog keep the plpgsql they were generated with. When a function is created or replaced under 3.0:
- a value interpolated where SQL needs an identifier (
FROM #{t}) is an error; write#{ident(t)}. In a static callCREATE FUNCTIONfails with a syntax error at the parenthesis plx writes around the value. In a dynamic call the statement fails when it runs, withsyntax error at or near "$k", where$kis the value’s parameter number; one static name position is not caught:UPDATE t SET #{c} = ...becomesSET (c) = ..., which PostgreSQL reads as a one-column list. With a plain value the statement fails when it runs; with a sub-SELECTorROW()it updates the column namedcwhatever the variable holds, as 2.x did. Write#{ident(c)}; - a value interpolated to build SQL text on purpose (
ORDER BY #{col} DESCwherecolholdsname) is now bound as data; write#{raw(col)}(or#{ident(col)}when it is a column name); '#{x}'now compares against the value ofx, not the string'x';- an expression interpolated outside quotes in a static call is one value:
SELECT #{a + b} * 10runs asSELECT (a + b) * 10. 2.x wroteSELECT a + b * 10, so a function that relied on the SQL splitting the expression computes a different result once re-created; - an escaped interpolation (
\$x,\${x},\#{x}) is plain text in the SQL; 2.x put a backslash and the value ofxthere.