On Thu, Mar 19, 2015 at 3:38 PM, Bruce Momjian <br...@momjian.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.
