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