Re: proposal: doc: simplify examples of dynamic SQL

On Thu, Mar 19, 2015 at 3:38 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> 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.
>
>
​
​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.

> > >> 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.
>

​Thank You!
​

>
> > 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.

Neither item requires attention but some food for thought.

David J.