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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
