On Thu, Mar 19, 2015 at 04:01:32PM -0700, David G. Johnston wrote:
> Doing some research on EXECUTE, I found that for constants, USING is
> best because it _conditionally_ quotes based on the data type, and for
> identifiers, format(%I) is best.
>
>
>
>
> On a nit-pick note, ISTM that "EXECUTE 'SELECT $1' USING ('1')"
>
> is not really "optionally quoted based on their data types" but rather
> processed in such a way as to not require quoting at all. Doesn't execute
> effectively bypass converting the USING values to text in much the same way as
> PREPARE/EXECUTE does in SQL? i.e., It uses the extended query protocol with a
> separate BIND instead of interpolating the arguments and then using a simple
> query protocol.
>
> Not that the reader likely cares - they just need to know to never place "%I,
> %L or $#" within quotes. I would say the same goes for %S always unless
> forced
> to do otherwise.
You are correct. I have modified that paragraph in the attached
version. Not only is %L inefficient, but converting to text can cause
errors, e.g. adding two strings throws an error:
test=> do $$ declare x text; begin execute format('select %L + ''2''',
1) into x; raise '%', x; end;$$;
ERROR: operator is not unique: unknown + unknown
LINE 1: select '1' + '2'
^
HINT: Could not choose a best candidate operator. You might need to
add explicit type casts.
QUERY: select '1' + '2'
CONTEXT: PL/pgSQL function inline_code_block line 1 at EXECUTE
statement
while adding an integer to a string works:
test=> do $$ declare x text; begin execute format('select $1 + ''2''')
using 1 into x; raise '%', x; end;$$;
ERROR: 3
> > The link above (40.5.4 in 9.4) is one such section where both forms need
> to
> > be showed but I would suggest reversing the order so that we first
> introduce
> > - prominently - the format function and then show the old-school way.
> That
> > said there is some merit to emphasizing the wrong and hard way so as to
> help
> > the reader conclude that the less painful format function really is
> their
> > best friend...but that would be my fallback position here.
>
> I tried showing format() first, but then it was odd about why to then
> show ||. I ended up showing || first, then showing format() and saying
> it is better.
>
>
> Prefacing it with: "You may also see the following syntax in the wild since
> format was only recently introduced."
>
> may solve your lack of reason for inclusion.
Uh, the problem with that is we are not going to revisit this when
format isn't "recently introduced". I think script writers naturally
think of query construction using string concatenation first, so showing
it first seems fine.
There are other places later in the docs where we explain all the quote*
functions and show examples of query construction using string
concatenation, but I am not sure how we can remove those.
--
Bruce Momjian <br...@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index 158d9d2..eb80169
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*************** EXECUTE 'SELECT count(*) FROM '
*** 1222,1227 ****
--- 1222,1234 ----
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:
+ <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)
--- 1304,1317 ----
</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 format('UPDATE tbl SET %I = %L W
*** 1399,1407 ****
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>
--- 1409,1417 ----
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.';
--- 2362,2369 ----
-- 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.
--- 2976,2983 ----
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>
--- 2986,2997 ----
<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
