On 10/2/14, 11:06 PM, 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.
Right. Duh.
>>> 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 point was that format() still isn't what we really need for dynamic SQL, and we should come up with something better.