Re: proposal: doc: simplify examples of dynamic SQL

On Fri, Mar 20, 2015 at 08:54:24AM -0700, David G. Johnston wrote:
> ​Looking at ​http://momjian.us/tmp/pgsql/plpgsql-statements.html#
> PLPGSQL-STATEMENTS-EXECUTING-DYN
>
> The paired example at the top of the patch has two things worth considering.
>
> 1. The layout of the format version is different, with respect to newlines,
> than the quote version; but while using newlines for the mandatory
> concatenation is good having an excessively long format string isn't desirable
> and so maybe we should show something like:
>
> EXECUTE format('SELECT count(*) FROM %I '
> || 'WHERE inserted_by = $1 AND insert <= $2', tabname)
> INTO c
> USING checked_user, checked_date

I think that is very confusing --- the idea is that we don't need to use
|| with format, but you are then using || to span multiple lines.

> 2. There is a recent posting pointing out the fact that the first query did not
> use quote_ident(tabname) but instead did tabname::regclass, which calls
> quote_ident internally.  While there is a choice is that situation with format
> you must pass in an unquoted label and so must not use tabname::regclass.  I
> think the first example should be written to use quote_ident(tabname).

Ah, good point. Updated patch attached, and URL updated.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +