Re: proposal: doc: simplify examples of dynamic SQL

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  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +