Re: proposal: doc: simplify examples of dynamic SQL

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.

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

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.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/proposal-doc-simplify-examples-of-dynamic-SQL-tp5821379p5821532.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.