From 0e1f6d8132b4da3e0727bb4918b9ea51446a9c41 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Tue, 24 Mar 2015 21:10:36 -0400 Subject: [PATCH] PL/pgSQL docs: recommend format() for query construction Previously only concatenation was recommended. Report by Pavel Stehule --- doc/src/sgml/func.sgml | 5 ++-- doc/src/sgml/plpgsql.sgml | 54 ++++++++++++++++++++++++--------------- 2 files changed, 37 insertions(+), 22 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index aa19e104d9..3195655d11 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -2998,14 +2998,15 @@ I treats the argument value as an SQL identifier, double-quoting it if necessary. - It is an error for the value to be null. + It is an error for the value to be null (equivalent to + quote_ident). L quotes the argument value as an SQL literal. A null value is displayed as the string NULL, without - quotes. + quotes (equivalent to quote_nullable). diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 158d9d2f22..9fc2a2f498 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1217,10 +1217,19 @@ EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= dynamically selected table, you could do this: EXECUTE 'SELECT count(*) FROM ' - || tabname::regclass + || quote_ident(tabname) || ' WHERE inserted_by = $1 AND inserted <= $2' INTO c USING checked_user, checked_date; + + A cleaner approach is to use format()'s %I + specification for table or column names (strings separated by a + newline are concatenated): + +EXECUTE format('SELECT count(*) FROM %I ' + 'WHERE inserted_by = $1 AND inserted <= $2', tabname) + INTO c + USING checked_user, checked_date; Another restriction on parameter symbols is that they only work in SELECT, INSERT, UPDATE, and @@ -1297,11 +1306,15 @@ EXECUTE 'SELECT count(*) FROM ' - Dynamic values that are to be inserted into the constructed - query require careful handling since they might themselves contain + Dynamic values require careful handling since they might contain quote characters. - An example (this assumes that you are using dollar quoting for the - function as a whole, so the quote marks need not be doubled): + An example using format() (this assumes that you are + dollar quoting the function body so quote marks need not be doubled): + +EXECUTE format('UPDATE tbl SET %I = $1 ' + 'WHERE key = $2', colname) USING newvalue, keyvalue; + + It is also possible to call the quoting functions directly: EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) @@ -1391,17 +1404,20 @@ EXECUTE 'UPDATE tbl SET ' format function (see ). For example: -EXECUTE format('UPDATE tbl SET %I = %L WHERE key = %L', colname, newvalue, keyvalue); +EXECUTE format('UPDATE tbl SET %I = %L ' + 'WHERE key = %L', colname, newvalue, keyvalue); + %I is equivalent to quote_ident, and + %L is equivalent to quote_nullable. The format function can be used in conjunction with the USING clause: EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname) USING newvalue, keyvalue; - This form is more efficient, because the parameters - newvalue and keyvalue are not - converted to text. + This form is better because the variables are handled in their native + data type format, rather than unconditionally converting them to + text and quoting them via %L. It is also more efficient. @@ -2352,10 +2368,8 @@ BEGIN -- Now "mviews" has one record from cs_materialized_views RAISE NOTICE 'Refreshing materialized view %s ...', quote_ident(mviews.mv_name); - EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name); - EXECUTE 'INSERT INTO ' - || quote_ident(mviews.mv_name) || ' ' - || mviews.mv_query; + EXECUTE format('TRUNCATE TABLE %I', mviews.mv_name); + EXECUTE format('INSERT INTO %I %s', mviews.mv_name, mviews.mv_query); END LOOP; RAISE NOTICE 'Done refreshing materialized views.'; @@ -2968,7 +2982,8 @@ OPEN unbound_cursorvar NO ), and it also means that variable substitution is not done on the command string. As with EXECUTE, parameter values - can be inserted into the dynamic command via USING. + can be inserted into the dynamic command via + format() and USING. The SCROLL and NO SCROLL options have the same meanings as for a bound cursor. @@ -2977,13 +2992,12 @@ OPEN unbound_cursorvar NO An example: -OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident(tabname) - || ' WHERE col1 = $1' USING keyvalue; +OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue; - In this example, the table name is inserted into the query textually, - so use of quote_ident() is recommended to guard against - SQL injection. The comparison value for col1 is inserted - via a USING parameter, so it needs no quoting. + In this example, the table name is inserted into the query via + format(). The comparison value for col1 + is inserted via a USING parameter, so it needs + no quoting. -- 2.40.0