Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2015-03-24 Thread Bruce Momjian
On Fri, Mar 20, 2015 at 04:28:38PM -0400, Bruce Momjian wrote: > On Fri, Mar 20, 2015 at 08:43:21AM -0700, David G. Johnston wrote: > > On Thu, Mar 19, 2015 at 6:49 PM, Bruce Momjian wrote: > > It is making a point about nulls and stuff.  There are later queries > > that use format(). > >

Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2015-03-20 Thread Bruce Momjian
On Fri, Mar 20, 2015 at 06:53:29PM -0700, David G. Johnston wrote: > On Friday, March 20, 2015, Bruce Momjian wrote: > > On Fri, Mar 20, 2015 at 05:50:03PM -0700, David G. Johnston wrote: > > ​I'm not sure that this particular feature of the standard is something > we > > should e

Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2015-03-20 Thread David G. Johnston
On Friday, March 20, 2015, Bruce Momjian wrote: > On Fri, Mar 20, 2015 at 05:50:03PM -0700, David G. Johnston wrote: > > ​I'm not sure that this particular feature of the standard is something > we > > should encourage. > > > > Its actually quite useful in this situation, and so maybe the novelty

Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2015-03-20 Thread Bruce Momjian
On Fri, Mar 20, 2015 at 05:50:03PM -0700, David G. Johnston wrote: > ​I'm not sure that this particular feature of the standard is something we > should encourage. > > Its actually quite useful in this situation, and so maybe the novelty is just > making me nervous,​ but the only reason I know of

Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2015-03-20 Thread David G. Johnston
On Fri, Mar 20, 2015 at 1:47 PM, Alvaro Herrera wrote: > Bruce Momjian wrote: > > On Fri, Mar 20, 2015 at 08:54:24AM -0700, David G. Johnston wrote: > > > > 1. The layout of the format version is different, with respect to > newlines, > > > than the quote version; but while using newlines for the

Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2015-03-20 Thread Bruce Momjian
On Fri, Mar 20, 2015 at 06:05:35PM -0300, Alvaro Herrera wrote: > Bruce Momjian wrote: > > On Fri, Mar 20, 2015 at 05:47:49PM -0300, Alvaro Herrera wrote: > > > > BTW very long lines are undesirable because they are truncated in the > > > PDF output. > > > > True, but the length was only 95 chara

Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2015-03-20 Thread Alvaro Herrera
Bruce Momjian wrote: > On Fri, Mar 20, 2015 at 05:47:49PM -0300, Alvaro Herrera wrote: > > BTW very long lines are undesirable because they are truncated in the > > PDF output. > > True, but the length was only 95 characters --- is that too long for our > PDFs? I built some PDFs when I did the A

Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2015-03-20 Thread Bruce Momjian
On Fri, Mar 20, 2015 at 05:47:49PM -0300, Alvaro Herrera wrote: > Bruce Momjian wrote: > > On Fri, Mar 20, 2015 at 08:54:24AM -0700, David G. Johnston wrote: > > > > 1. The layout of the format version is different, with respect to > > > newlines, > > > than the quote version; but while using new

Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2015-03-20 Thread Alvaro Herrera
Bruce Momjian wrote: > On Fri, Mar 20, 2015 at 08:54:24AM -0700, David G. Johnston wrote: > > 1. The layout of the format version is different, with respect to newlines, > > than the quote version; but while using newlines for the mandatory > > concatenation is good having an excessively long form

Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2015-03-20 Thread Bruce Momjian
On Fri, Mar 20, 2015 at 08:54:24AM -0700, David G. Johnston wrote: > ​Looking at ​http://momjian.us/tmp/pgsql/plpgsql-statements.html# > PLPGSQL-STATEMENTS-EXECUTING-DYN > > The paired example at the top of the patch has two things worth considering. > > 1. The layout of the format version is dif

Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2015-03-20 Thread Bruce Momjian
On Fri, Mar 20, 2015 at 08:43:21AM -0700, David G. Johnston wrote: > On Thu, Mar 19, 2015 at 6:49 PM, Bruce Momjian wrote: > It is making a point about nulls and stuff.  There are later queries > that use format(). > > I thought maybe you meant those but your specific mention of "​ > ​ > T

Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2015-03-20 Thread David G. Johnston
​Looking at ​ http://momjian.us/tmp/pgsql/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN The paired example at the top of the patch has two things worth considering. 1. The layout of the format version is different, with respect to newlines, than the quote version; but while using newli

Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2015-03-20 Thread David G. Johnston
On Thu, Mar 19, 2015 at 6:49 PM, Bruce Momjian wrote: > On Thu, Mar 19, 2015 at 06:05:52PM -0700, David G. Johnston wrote: > > On Thu, Mar 19, 2015 at 5:18 PM, Bruce Momjian wrote: > > > ​​ > There are other places later in the docs where we explain all the quote* > > functions and show exam

Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2015-03-19 Thread Bruce Momjian
On Thu, Mar 19, 2015 at 06:05:52PM -0700, David G. Johnston wrote: > On Thu, Mar 19, 2015 at 5:18 PM, Bruce Momjian wrote: > There are other places later in the docs where we explain all the quote* > functions and show examples of query construction using string > concatenation, but I

Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2015-03-19 Thread David G. Johnston
On Thu, Mar 19, 2015 at 5:18 PM, Bruce Momjian wrote: > On Thu, Mar 19, 2015 at 04:01:32PM -0700, David G. Johnston wrote: > > ​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 incl

Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2015-03-19 Thread Bruce Momjian
On Thu, Mar 19, 2015 at 04:01:32PM -0700, David G. Johnston wrote: > 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

Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2015-03-19 Thread David G. Johnston
On Thu, Mar 19, 2015 at 3:38 PM, Bruce Momjian wrote: > 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,

Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2015-03-19 Thread Bruce Momjian
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 f

Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2014-10-04 Thread Jim Nasby
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 k

Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2014-10-02 Thread Pavel Stehule
2014-10-03 6:06 GMT+02:00 David G Johnston : > 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 SE

Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2014-10-02 Thread Pavel Stehule
Hi 2014-10-03 5:16 GMT+02:00 Jim Nasby : > 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 > No it isn't. I is 100% safe > EXECUTE format('UPDATE tbl

Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2014-10-02 Thread David G Johnston
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', >> coln

Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL

2014-10-02 Thread Jim Nasby
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, b

[HACKERS] proposal: doc: simplify examples of dynamic SQL

2014-10-02 Thread Pavel Stehule
Hi There are few less readable examples of dynamic SQL in plpgsql doc like: EXECUTE 'SELECT count(*) FROM ' || tabname::regclass || ' WHERE inserted_by = $1 AND inserted <= $2' INTO c USING checked_user, checked_date; or EXECUTE 'UPDATE tbl SET ' || quote_ident(colname)