On Thu, Oct 2, 2014 at 09:06:54PM -0700, David G Johnston wrote:
> Jim Nasby-5 wrote
> > On 10/2/14, 6:51 AM, Pavel Stehule wrote:
> >> EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = %L',
> >> colname, keyvalue)
> >> or
> > -1, because of quoting issues
> >> EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = $1',
> >> colname)
> >> USING keyvalue;
> > Better, but I think it should really be quote_ident( colname )
>
> http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE
>
> The use of %I and %L solve all quoting issues when using format(); they
> likely call the relevant quote_ function on the user's behalf.
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.
> >> A old examples are very instructive, but little bit less readable and
> >> maybe too complex for beginners.
> >>
> >> Opinions?
> > Honestly, I'm not to fond of either. format() is a heck of a lot nicer
> > than a forest of ||'s, but I think it still falls short of what we'd
> > really want here which is some kind of variable substitution or even a
> > templating language. IE:
> >
> > EXECUTE 'UDPATE tbl SET $colname = newvalue WHERE key = $keyvalue';
>
> Putting that example into the docs isn't a good idea...it isn't valid in
> PostgreSQL ;)
>
>
> My complaint with the topic is that it is not specific enough. There are
> quite a few locations with dynamic queries. My take is that the
> concatenation form be shown only in "possible ways to accomplish this" type
> sections but that all actual examples or recommendations make use of the
> format function.
I have done this with the attached PL/pgSQL doc patch.
> 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.
--
Bruce Momjian <[email protected]> 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..52b4daa
*** 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 <emphasis>optionally</>
! quoted based on their data types, rather than unconditionally quoted
! 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
