@@ -1217,10 +1217,19 @@ EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <=
12171217 dynamically selected table, you could do this:
12181218<programlisting>
12191219EXECUTE 'SELECT count(*) FROM '
1220- || tabname::regclass
1220+ ||quote_ident( tabname)
12211221 || ' WHERE inserted_by = $1 AND inserted <= $2'
12221222 INTO c
12231223 USING checked_user, checked_date;
1224+ </programlisting>
1225+ A cleaner approach is to use <function>format()</>'s <literal>%I</>
1226+ specification for table or column names (strings separated by a
1227+ newline are concatenated):
1228+ <programlisting>
1229+ EXECUTE format('SELECT count(*) FROM %I '
1230+ 'WHERE inserted_by = $1 AND inserted <= $2', tabname)
1231+ INTO c
1232+ USING checked_user, checked_date;
12241233</programlisting>
12251234 Another restriction on parameter symbols is that they only work in
12261235 <command>SELECT</>, <command>INSERT</>, <command>UPDATE</>, and
@@ -1297,11 +1306,15 @@ EXECUTE 'SELECT count(*) FROM '
12971306 </para>
12981307
12991308 <para>
1300- Dynamic values that are to be inserted into the constructed
1301- query require careful handling since they might themselves contain
1309+ Dynamic values require careful handling since they might contain
13021310 quote characters.
1303- An example (this assumes that you are using dollar quoting for the
1304- function as a whole, so the quote marks need not be doubled):
1311+ An example using <function>format()</> (this assumes that you are
1312+ dollar quoting the function body so quote marks need not be doubled):
1313+ <programlisting>
1314+ EXECUTE format('UPDATE tbl SET %I = $1 '
1315+ 'WHERE key = $2', colname) USING newvalue, keyvalue;
1316+ </programlisting>
1317+ It is also possible to call the quoting functions directly:
13051318<programlisting>
13061319EXECUTE 'UPDATE tbl SET '
13071320 || quote_ident(colname)
@@ -1391,17 +1404,20 @@ EXECUTE 'UPDATE tbl SET '
13911404 <function>format</function> function (see <xref
13921405 linkend="functions-string">). For example:
13931406<programlisting>
1394- EXECUTE format('UPDATE tbl SET %I = %L WHERE key = %L', colname, newvalue, keyvalue);
1407+ EXECUTE format('UPDATE tbl SET %I = %L '
1408+ 'WHERE key = %L', colname, newvalue, keyvalue);
13951409</programlisting>
1410+ <literal>%I</> is equivalent to <function>quote_ident</>, and
1411+ <literal>%L</> is equivalent to <function>quote_nullable</function>.
13961412 The <function>format</function> function can be used in conjunction with
13971413 the <literal>USING</literal> clause:
13981414<programlisting>
13991415EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
14001416 USING newvalue, keyvalue;
14011417</programlisting>
1402- This form ismore efficient, because theparameters
1403- <literal>newvalue</literal> and <literal>keyvalue</literal> are not
1404- converted to text .
1418+ This form isbetter because thevariables are handled in their native
1419+ data type format, rather than unconditionally converting them to
1420+ text and quoting them via <literal>%L</>. It is also more efficient .
14051421 </para>
14061422 </example>
14071423
@@ -2352,10 +2368,8 @@ BEGIN
23522368 -- Now "mviews" has one record from cs_materialized_views
23532369
23542370 RAISE NOTICE 'Refreshing materialized view %s ...', quote_ident(mviews.mv_name);
2355- EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
2356- EXECUTE 'INSERT INTO '
2357- || quote_ident(mviews.mv_name) || ' '
2358- || mviews.mv_query;
2371+ EXECUTE format('TRUNCATE TABLE %I', mviews.mv_name);
2372+ EXECUTE format('INSERT INTO %I %s', mviews.mv_name, mviews.mv_query);
23592373 END LOOP;
23602374
23612375 RAISE NOTICE 'Done refreshing materialized views.';
@@ -2968,7 +2982,8 @@ OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </opt
29682982 from one run to the next (see <xref linkend="plpgsql-plan-caching">),
29692983 and it also means that variable substitution is not done on the
29702984 command string. As with <command>EXECUTE</command>, parameter values
2971- can be inserted into the dynamic command via <literal>USING</>.
2985+ can be inserted into the dynamic command via
2986+ <literal>format()</> and <literal>USING</>.
29722987 The <literal>SCROLL</> and
29732988 <literal>NO SCROLL</> options have the same meanings as for a bound
29742989 cursor.
@@ -2977,13 +2992,12 @@ OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </opt
29772992 <para>
29782993 An example:
29792994<programlisting>
2980- OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident(tabname)
2981- || ' WHERE col1 = $1' USING keyvalue;
2995+ OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;
29822996</programlisting>
2983- In this example, the table name is inserted into the querytextually,
2984- so use of <function>quote_ident ()</> is recommended to guard against
2985- SQL injection. The comparison value for <literal>col1 </>is inserted
2986- via a <literal>USING</> parameter, so it needs no quoting.
2997+ In this example, the table name is inserted into the queryvia
2998+ <function>format ()</>. The comparison value for <literal>col1</>
2999+ is inserted via a <literal>USING </>parameter, so it needs
3000+ no quoting.
29873001 </para>
29883002 </sect3>
29893003