Re: proposal: doc: simplify examples of dynamic SQL - Mailing list pgsql-hackers
| From | David G. Johnston |
|---|---|
| Subject | Re: proposal: doc: simplify examples of dynamic SQL |
| Date | |
| Msg-id | CAKFQuwaGe6iwDxFjXsbvPC_+4EdB-_rLANevgn4oxpfUS0nD-w@mail.gmail.com Whole thread Raw |
| In response to | Re: proposal: doc: simplify examples of dynamic SQL (Bruce Momjian <bruce@momjian.us>) |
| Responses |
Re: proposal: doc: simplify examples of dynamic SQL
|
| List | pgsql-hackers |
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.
pgsql-hackers by date: