On Fri, Mar 20, 2015 at 05:50:03PM -0700, David G. Johnston wrote:
> I'm not sure that this particular feature of the standard is something we
> should encourage.
>
> Its actually quite useful in this situation, and so maybe the novelty is just
> making me nervous, but the only reason I know of this behavior is because
> I've
> seen a number of posts in just the past couple of years when people
> accidentally used this feature and then were surprised when they didn't get an
> error. If this stays I would suggest that we take the opportunity to
> cross-reference back to where the syntax is defined so people aren't left
> scratching their heads as to why it works - or why if they remove the newline
> in their own attempt the code suddenly breaks.
Yeah, I am kind on the fence about it, but it is a nice feature,
particulary for PL/pgSQL programs. I added a mention of the string
concatentation feature --- patch attached, and URL updated.
--
Bruce Momjian <br...@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index aa19e10..3195655
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 2998,3011 ****
<para>
<literal>I</literal> treats the argument value as an SQL
identifier, double-quoting it if necessary.
! It is an error for the value to be null.
</para>
</listitem>
<listitem>
<para>
<literal>L</literal> quotes the argument value as an SQL literal.
A null value is displayed as the string <literal>NULL</>, without
! quotes.
</para>
</listitem>
</itemizedlist>
--- 2998,3012 ----
<para>
<literal>I</literal> treats the argument value as an SQL
identifier, double-quoting it if necessary.
! It is an error for the value to be null (equivalent to
! <function>quote_ident</>).
</para>
</listitem>
<listitem>
<para>
<literal>L</literal> quotes the argument value as an SQL literal.
A null value is displayed as the string <literal>NULL</>, without
! quotes (equivalent to <function>quote_nullable</function>).
</para>
</listitem>
</itemizedlist>
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index 158d9d2..9fc2a2f
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*************** EXECUTE 'SELECT count(*) FROM mytable WH
*** 1217,1227 ****
dynamically selected table, you could do this:
<programlisting>
EXECUTE 'SELECT count(*) FROM '
! || tabname::regclass
|| ' WHERE inserted_by = $1 AND inserted <= $2'
INTO c
USING checked_user, checked_date;
</programlisting>
Another restriction on parameter symbols is that they only work in
<command>SELECT</>, <command>INSERT</>, <command>UPDATE</>, and
<command>DELETE</> commands. In other statement
--- 1217,1236 ----
dynamically selected table, you could do this:
<programlisting>
EXECUTE 'SELECT count(*) FROM '
! || quote_ident(tabname)
|| ' WHERE inserted_by = $1 AND inserted <= $2'
INTO c
USING checked_user, checked_date;
</programlisting>
+ A cleaner approach is to use <function>format()</>'s <literal>%I</>
+ specification for table or column names (strings separated by a
+ newline are concatenated):
+ <programlisting>
+ EXECUTE format('SELECT count(*) FROM %I '
+ 'WHERE inserted_by = $1 AND inserted <= $2', tabname)
+ INTO c
+ USING checked_user, checked_date;
+ </programlisting>
Another restriction on parameter symbols is that they only work in
<command>SELECT</>, <command>INSERT</>, <command>UPDATE</>, and
<command>DELETE</> commands. In other statement
*************** EXECUTE 'SELECT count(*) FROM '
*** 1297,1307 ****
</para>
<para>
! Dynamic values that are to be inserted into the constructed
! query require careful handling since they might themselves 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):
<programlisting>
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
--- 1306,1320 ----
</para>
<para>
! Dynamic values require careful handling since they might contain
quote characters.
! An example using <function>format()</> (this assumes that you are
! dollar quoting the function body so quote marks need not be doubled):
! <programlisting>
! EXECUTE format('UPDATE tbl SET %I = $1 '
! 'WHERE key = $2', colname) USING newvalue, keyvalue;
! </programlisting>
! It is also possible to call the quoting functions directly:
<programlisting>
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
*************** EXECUTE 'UPDATE tbl SET '
*** 1391,1407 ****
<function>format</function> function (see <xref
linkend="functions-string">). For example:
<programlisting>
! EXECUTE format('UPDATE tbl SET %I = %L WHERE key = %L', colname, newvalue, keyvalue);
</programlisting>
The <function>format</function> function can be used in conjunction with
the <literal>USING</literal> clause:
<programlisting>
EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
USING newvalue, keyvalue;
</programlisting>
! This form is more efficient, because the parameters
! <literal>newvalue</literal> and <literal>keyvalue</literal> are not
! converted to text.
</para>
</example>
--- 1404,1423 ----
<function>format</function> function (see <xref
linkend="functions-string">). For example:
<programlisting>
! EXECUTE format('UPDATE tbl SET %I = %L '
! 'WHERE key = %L', colname, newvalue, keyvalue);
</programlisting>
+ <literal>%I</> is equivalent to <function>quote_ident</>, and
+ <literal>%L</> is equivalent to <function>quote_nullable</function>.
The <function>format</function> function can be used in conjunction with
the <literal>USING</literal> clause:
<programlisting>
EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
USING newvalue, keyvalue;
</programlisting>
! 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 <literal>%L</>. It is also more efficient.
</para>
</example>
*************** BEGIN
*** 2352,2361 ****
-- 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;
END LOOP;
RAISE NOTICE 'Done refreshing materialized views.';
--- 2368,2375 ----
-- Now "mviews" has one record from cs_materialized_views
RAISE NOTICE 'Refreshing materialized view %s ...', quote_ident(mviews.mv_name);
! 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.';
*************** OPEN <replaceable>unbound_cursorvar</rep
*** 2968,2974 ****
from one run to the next (see <xref linkend="plpgsql-plan-caching">),
and it also means that variable substitution is not done on the
command string. As with <command>EXECUTE</command>, parameter values
! can be inserted into the dynamic command via <literal>USING</>.
The <literal>SCROLL</> and
<literal>NO SCROLL</> options have the same meanings as for a bound
cursor.
--- 2982,2989 ----
from one run to the next (see <xref linkend="plpgsql-plan-caching">),
and it also means that variable substitution is not done on the
command string. As with <command>EXECUTE</command>, parameter values
! can be inserted into the dynamic command via
! <literal>format()</> and <literal>USING</>.
The <literal>SCROLL</> and
<literal>NO SCROLL</> options have the same meanings as for a bound
cursor.
*************** OPEN <replaceable>unbound_cursorvar</rep
*** 2977,2989 ****
<para>
An example:
<programlisting>
! OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident(tabname)
! || ' WHERE col1 = $1' USING keyvalue;
</programlisting>
! In this example, the table name is inserted into the query textually,
! so use of <function>quote_ident()</> is recommended to guard against
! SQL injection. The comparison value for <literal>col1</> is inserted
! via a <literal>USING</> parameter, so it needs no quoting.
</para>
</sect3>
--- 2992,3003 ----
<para>
An example:
<programlisting>
! OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;
</programlisting>
! In this example, the table name is inserted into the query via
! <function>format()</>. The comparison value for <literal>col1</>
! is inserted via a <literal>USING</> parameter, so it needs
! no quoting.
</para>
</sect3>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
