proposal: doc: simplify examples of dynamic SQL

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)
|| ' = $'
|| newvalue
|| '$ WHERE key = '
|| quote_literal(keyvalue);

We can show a examples based on "format" function only:

EXECUTE format('SELECT count(*) FROM %I'
' WHERE inserted_by = $1 AND inserted <= $2',
tabname)
INTO c
USING checked_user, checked_date;

or

EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = %L',
colname, keyvalue)
or

EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = $1',
colname)
USING keyvalue;

A old examples are very instructive, but little bit less readable and maybe
too complex for beginners.

Opinions?

Regards

Pavel

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 )
> 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';

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.

Hi

2014-10-03 5:16 GMT+02:00 Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>:

> 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 SET %I = newvalue WHERE key = $1',
>> colname)
>> USING keyvalue;
>>
> Better, but I think it should really be quote_ident( colname )
>
>> 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';
>

Your proposal significantly increase a work with string. Escaping and
quoting depends on context, and should be different in different context.
In PHP or Perl, this technique is the most simple backdoor for SQL
injection.

Pavel

2014-10-03 6:06 GMT+02:00 David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>:

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

It is a good idea.

Regards

Pavel

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

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.

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

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.

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

I have done this with the attached PL/pgSQL doc patch.

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

I tried showing format() first, but then it was odd about why to then
show ||. I ended up showing || first, then showing format() and saying
it is better.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Thu, Mar 19, 2015 at 3:38 PM, Bruce Momjian <bruce(at)momjian(dot)us> 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, 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.
>
> 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 that "EXECUTE 'SELECT $1' USING ('1')"​
​
​ is not really "optionally quoted based on their data types" but rather
processed in such a way as to not require quoting at all. Doesn't execute
effectively bypass converting the USING values to text in much the same way
as PREPARE/EXECUTE does in SQL? i.e., It uses the extended query protocol
with a separate BIND instead of interpolating the arguments and then using
a simple query protocol.

Not that the reader likely cares - they just need to know to never place
"%I, %L or $#" within quotes. I would say the same goes for %S always
unless forced to do otherwise.

> > >> 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.
>
> I have done this with the attached PL/pgSQL doc patch.
>

​Thank You!
​

>
> > 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.
>
> I tried showing format() first, but then it was odd about why to then
> show ||. I ended up showing || first, then showing format() and saying
> it is better.
>

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

Neither item requires attention but some food for thought.

David J.

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 that "EXECUTE 'SELECT $1' USING ('1')"​
> ​
> ​ is not really "optionally quoted based on their data types" but rather
> processed in such a way as to not require quoting at all.  Doesn't execute
> effectively bypass converting the USING values to text in much the same way as
> PREPARE/EXECUTE does in SQL?  i.e., It uses the extended query protocol with a
> separate BIND instead of interpolating the arguments and then using a simple
> query protocol.
>
> Not that the reader likely cares - they just need to know to never place "%I,
> %L or $#" within quotes.  I would say the same goes for %S always unless forced
> to do otherwise.

You are correct. I have modified that paragraph in the attached
version. Not only is %L inefficient, but converting to text can cause
errors, e.g. adding two strings throws an error:

test=> do $$ declare x text; begin execute format('select %L + ''2''', 1) into x; raise '%', x; end;$$;
ERROR: operator is not unique: unknown + unknown
LINE 1: select '1' + '2'
^
HINT: Could not choose a best candidate operator. You might need to add explicit type casts.
QUERY: select '1' + '2'
CONTEXT: PL/pgSQL function inline_code_block line 1 at EXECUTE statement

while adding an integer to a string works:

test=> do $$ declare x text; begin execute format('select $1 + ''2''') using 1 into x; raise '%', x; end;$$;
ERROR: 3

> > 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.
>
> I tried showing format() first, but then it was odd about why to then
> show ||.  I ended up showing || first, then showing format() and saying
> it is better.
>
>
> ​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 inclusion.

Uh, the problem with that is we are not going to revisit this when
format isn't "recently introduced". I think script writers naturally
think of query construction using string concatenation first, so showing
it first seems fine.

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 am not sure how we can remove those.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Thu, Mar 19, 2015 at 5:18 PM, Bruce Momjian <bruce(at)momjian(dot)us> 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 inclusion.
>
> Uh, the problem with that is we are not going to revisit this when
> format isn't "recently introduced". I think script writers naturally
> think of query construction using string concatenation first, so showing
> it first seems fine.
>
>
​+1​

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 am not sure how we can remove those.
>
>
​Can you be more specific?

On a related note:

"If you are dealing with values that might be null, you should usually use
quote_nullable in place of quote_literal."

Its unclear why, aside from semantic uncleanliness, someone would use
quote_literal given its identical behavior for non-null values and inferior
behavior which passed NULL. The function table for the two could maybe be
more clear since quote_nullable(NULL) returns a string representation of
NULL without any quotes while quote_literal(NULL) returns an actual NULL
that ultimately poisons the string concatenation that these functions are
used with.

<reads some more>

The differences between the actual null and the string NULL are strictly in
capitalization - which is not consistent even within the table. concat_ws
states "NULL arguments are ignored" and so represents actual null with
all-caps which is string NULL in the quote_* descriptions. Having read
40.5.4 and example 40-1 the difference is clear and obvious so maybe what
is in the table is sufficient for this topic.

I would suggest adding a comment to quote_ident and quote_nullable that
corresponding format codes are %I and %L. Obviously there is no "quote_"
function to correspond with %S. There is likewise nor corresponding format
code for quote_literal since quote_nullable is superior in every way (that
I can tell at least).

David J.

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 <bruce(at)momjian(dot)us> 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 am not sure how we can remove those.
>
>
>
> ​Can you be more specific?

Yes. You can see the output of the attached patch here:

http://momjian.us/tmp/pgsql/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Notice:

EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = '
|| quote_nullable(newvalue)
|| ' WHERE key = '
|| quote_nullable(keyvalue);

and

EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = $$'
|| newvalue
|| '$$ WHERE key = '
|| quote_literal(keyvalue);

It is making a point about nulls and stuff. There are later queries
that use format().

> On a related note:
>
> "If you are dealing with values that might be null, you should usually use
> quote_nullable in place of quote_literal."
>
> Its unclear why, aside from semantic uncleanliness, someone would use
> quote_literal given its identical behavior for non-null values and inferior
> behavior which passed NULL.  The function table for the two could maybe be more
> clear since quote_nullable(NULL) returns a string representation of NULL
> without any quotes while quote_literal(NULL) returns an actual NULL that
> ultimately poisons the string concatenation that these functions are used with.
>
> <reads some more>
>
> The differences between the actual null and the string NULL are strictly in
> capitalization - which is not consistent even within the table.  concat_ws
> states "NULL arguments are ignored" and so represents actual null with all-caps
> which is string NULL in the quote_* descriptions.  Having read 40.5.4 and
> example 40-1 the difference is clear and obvious so maybe what is in the table
> is sufficient for this topic.
>
> I would suggest adding a comment to quote_ident and quote_nullable that
> corresponding format codes are %I and %L.  Obviously there is no "quote_"
> function to correspond with %S.  There is likewise nor corresponding format
> code for quote_literal since quote_nullable is superior in every way (that I
> can tell at least).

OK, I have added that tip --- good suggestion. Patch attached.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Thu, Mar 19, 2015 at 6:49 PM, Bruce Momjian <bruce(at)momjian(dot)us> 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 <bruce(at)momjian(dot)us> 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 am not sure how we can remove those.
> >
> >
> >
> > ​Can you be more specific?
>
> Yes. You can see the output of the attached patch here:
>
>
> http://momjian.us/tmp/pgsql/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
>
> Notice:
>
> EXECUTE 'UPDATE tbl SET '
> || quote_ident(colname)
> || ' = '
> || quote_nullable(newvalue)
> || ' WHERE key = '
> || quote_nullable(keyvalue);
>
> and
>
> EXECUTE 'UPDATE tbl SET '
> || quote_ident(colname)
> || ' = $$'
> || newvalue
> || '$$ WHERE key = '
> || quote_literal(keyvalue);
>
> 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 "​
​
There are other places later in the docs" confused me since you made
changes before and after that specific section.

Those examples need to be somewhere and it doesn't seem like a undesireable
enough setup that major reconstructive surgery is warranted to try and move
them elsewhere.

> > On a related note:
> >
> > "If you are dealing with values that might be null, you should usually
> use
> > quote_nullable in place of quote_literal."
> >
> > Its unclear why, aside from semantic uncleanliness, someone would use
> > quote_literal given its identical behavior for non-null values and
> inferior
> > behavior which passed NULL. The function table for the two could maybe
> be more
> > clear since quote_nullable(NULL) returns a string representation of NULL
> > without any quotes while quote_literal(NULL) returns an actual NULL that
> > ultimately poisons the string concatenation that these functions are
> used with.
> >
> > <reads some more>
> >
> > The differences between the actual null and the string NULL are strictly
> in
> > capitalization - which is not consistent even within the table.
> concat_ws
> > states "NULL arguments are ignored" and so represents actual null with
> all-caps
> > which is string NULL in the quote_* descriptions. Having read 40.5.4 and
> > example 40-1 the difference is clear and obvious so maybe what is in the
> table
> > is sufficient for this topic.
> >
> > I would suggest adding a comment to quote_ident and quote_nullable that
> > corresponding format codes are %I and %L. Obviously there is no "quote_"
> > function to correspond with %S. There is likewise nor corresponding
> format
> > code for quote_literal since quote_nullable is superior in every way
> (that I
> > can tell at least).
>
> OK, I have added that tip --- good suggestion. Patch attached.
>
>
I was actually referring to chapter 9

http://www.postgresql.org/docs/9.4/interactive/functions-string.html

​The table definitions of the quote_* function should have a comment about
their equivalency to format %I and %L

Also, ​in 9.4.1 (format -> type) would be the most obvious place for the
equivalency of the format %I and %L to quote_*

IMO too much is trying to be done within example 40-1 (for instance, the
quote_literal/nullable explanation should be moved elsewhere); and while
these are mainly useful with dynamic SQL it still behooves us to put the
definition stuff in the structural area and then use the example for
comprehension and clarification regarding best practices (i.e., format for
%I but USING for literals - though I know some would say we should
necessarily express those kinds of opinions in the docs...). That said, it
is not as bad as I may seem to be making it out to be and aside from
wanting to put and obvious reference to format directly next to the quote_*
functions is more style that content. The desire for the linkage is strong
though because we want someone who naturally would use string concatenation
and the quote_* functions to be made aware of, and convinced to use (they
will thank us for this), the format() function instead.

David J.

​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 newlines for the mandatory
concatenation is good having an excessively long format string isn't
desirable and so maybe we should show something like:

EXECUTE format('SELECT count(*) FROM %I '
|| 'WHERE inserted_by = $1 AND insert <= $2', tabname)
INTO c
USING checked_user, checked_date

2. There is a recent posting pointing out the fact that the first query did
not use quote_ident(tabname) but instead did tabname::regclass, which calls
quote_ident internally. While there is a choice is that situation with
format you must pass in an unquoted label and so must not use
tabname::regclass. I think the first example should be written to use
quote_ident(tabname).

As regards the ::regclass behavior I would need see it current treatment
and recommended usage in the docs in order to form an opinion on how it
interacts with quote_literal and %I.

David J.

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 <bruce(at)momjian(dot)us> 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 "​
> ​
> There are other places later in the docs" confused me since you made changes
> before and after that specific section.
>
> Those examples need to be somewhere and it doesn't seem like a undesireable
> enough setup that major reconstructive surgery is warranted to try and move
> them elsewhere.

Yes, agreed.

> I was actually referring to chapter 9
>
> http://www.postgresql.org/docs/9.4/interactive/functions-string.html
>
> ​The table definitions of the quote_* function should have a comment about
> their equivalency to format %I and %L

I think it is going to be awkward to mention a much more complex
function, format(), when covering a simle quote function.

> Also, ​in 9.4.1 (format -> type) would be the most obvious place for the
> equivalency of the format %I and %L to quote_*

Yes, added.

Update patch attached and URL udpated with current patch too.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

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 different, with respect to newlines,
> than the quote version; but while using newlines for the mandatory
> concatenation is good having an excessively long format string isn't desirable
> and so maybe we should show something like:
>
> EXECUTE format('SELECT count(*) FROM %I '
> || 'WHERE inserted_by = $1 AND insert <= $2', tabname)
> INTO c
> USING checked_user, checked_date

I think that is very confusing --- the idea is that we don't need to use
|| with format, but you are then using || to span multiple lines.

> 2. There is a recent posting pointing out the fact that the first query did not
> use quote_ident(tabname) but instead did tabname::regclass, which calls
> quote_ident internally.  While there is a choice is that situation with format
> you must pass in an unquoted label and so must not use tabname::regclass.  I
> think the first example should be written to use quote_ident(tabname).

Ah, good point. Updated patch attached, and URL updated.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

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 format string isn't desirable
> > and so maybe we should show something like:
> >
> > EXECUTE format('SELECT count(*) FROM %I '
> > || 'WHERE inserted_by = $1 AND insert <= $2', tabname)
> > INTO c
> > USING checked_user, checked_date
>
> I think that is very confusing --- the idea is that we don't need to use
> || with format, but you are then using || to span multiple lines.

That || seems fine, since it's only used for a line continuation; having
|| scattered all over the query string to interpolate each variable is
much more unreadable.

That said, the || there is unnecessary because per standard two literals 'lit1'
'lit2'
are concatenated if they are separated by a newline. So this

EXECUTE format('SELECT count(*) FROM %I '
'WHERE inserted_by = $1 AND insert <= $2', tabname)
INTO c
USING checked_user, checked_date

should suffice.

BTW very long lines are undesirable because they are truncated in the
PDF output.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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 newlines for the mandatory
> > > concatenation is good having an excessively long format string isn't desirable
> > > and so maybe we should show something like:
> > >
> > > EXECUTE format('SELECT count(*) FROM %I '
> > > || 'WHERE inserted_by = $1 AND insert <= $2', tabname)
> > > INTO c
> > > USING checked_user, checked_date
> >
> > I think that is very confusing --- the idea is that we don't need to use
> > || with format, but you are then using || to span multiple lines.
>
> That || seems fine, since it's only used for a line continuation; having
> || scattered all over the query string to interpolate each variable is
> much more unreadable.
>
> That said, the || there is unnecessary because per standard two literals 'lit1'
> 'lit2'
> are concatenated if they are separated by a newline. So this
>
> EXECUTE format('SELECT count(*) FROM %I '
> 'WHERE inserted_by = $1 AND insert <= $2', tabname)
> INTO c
> USING checked_user, checked_date
>
> should suffice.

OK, I used your idea, patch attached.

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

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

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 ALTER USER CURRENT_USER patch, and it
seemed to me that synposes ought to limit to about 85 chars. Maybe some
more still fit in the page, but I think 95 would be a bit too much.
I'm not sure how the code you're writing here is indented, though; you'd
have to build it and give it a look.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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 characters --- is that too long for our
> > PDFs?
>
> I built some PDFs when I did the ALTER USER CURRENT_USER patch, and it
> seemed to me that synposes ought to limit to about 85 chars. Maybe some
> more still fit in the page, but I think 95 would be a bit too much.
> I'm not sure how the code you're writing here is indented, though; you'd
> have to build it and give it a look.

OK, thanks, good to know.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Fri, Mar 20, 2015 at 1:47 PM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
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 mandatory
> > > concatenation is good having an excessively long format string isn't
> desirable
> > > and so maybe we should show something like:
> > >
> > > EXECUTE format('SELECT count(*) FROM %I '
> > > || 'WHERE inserted_by = $1 AND insert <= $2', tabname)
> > > INTO c
> > > USING checked_user, checked_date
> >
> > I think that is very confusing --- the idea is that we don't need to use
> > || with format, but you are then using || to span multiple lines.
>
> That || seems fine, since it's only used for a line continuation; having
> || scattered all over the query string to interpolate each variable is
> much more unreadable.
>
> That said, the || there is unnecessary because per standard two literals
> 'lit1'
> 'lit2'
> are concatenated if they are separated by a newline. So this
>
> EXECUTE format('SELECT count(*) FROM %I '
> 'WHERE inserted_by = $1 AND insert <= $2', tabname)
> INTO c
> USING checked_user, checked_date
>
> should suffice.
>

​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 this behavior is
because I've seen a number of posts in just the past couple of years when
people accidentally used this feature and then were surprised when they
didn't get an error. If this stays I would suggest that we take the
opportunity to cross-reference back to where the syntax is defined so
people aren't left scratching their heads as to why it works - or why if
they remove the newline in their own attempt the code suddenly breaks.

David J.

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 this behavior is because I've
> seen a number of posts in just the past couple of years when people
> accidentally used this feature and then were surprised when they didn't get an
> error.  If this stays I would suggest that we take the opportunity to
> cross-reference back to where the syntax is defined so people aren't left
> scratching their heads as to why it works - or why if they remove the newline
> in their own attempt the code suddenly breaks.

Yeah, I am kind on the fence about it, but it is a nice feature,
particulary for PL/pgSQL programs. I added a mention of the string
concatentation feature --- patch attached, and URL updated.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Friday, March 20, 2015, Bruce Momjian <bruce(at)momjian(dot)us> 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 is
> just
> > making me nervous,​ but the only reason I know of this behavior is
> because I've
> > seen a number of posts in just the past couple of years when people
> > accidentally used this feature and then were surprised when they didn't
> get an
> > error. If this stays I would suggest that we take the opportunity to
> > cross-reference back to where the syntax is defined so people aren't left
> > scratching their heads as to why it works - or why if they remove the
> newline
> > in their own attempt the code suddenly breaks.
>
> Yeah, I am kind on the fence about it, but it is a nice feature,
> particulary for PL/pgSQL programs. I added a mention of the string
> concatentation feature --- patch attached, and URL updated.
>
>
The third option is to just embed a new line in the string itself.

Execute Format(’...
...', tbl)
USING val

David J.

On Fri, Mar 20, 2015 at 06:53:29PM -0700, David G. Johnston wrote:
> On Friday, March 20, 2015, Bruce Momjian <bruce(at)momjian(dot)us> 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 is
> just
> > making me nervous,​ but the only reason I know of this behavior is
> because I've
> > seen a number of posts in just the past couple of years when people
> > accidentally used this feature and then were surprised when they didn't
> get an
> > error.  If this stays I would suggest that we take the opportunity to
> > cross-reference back to where the syntax is defined so people aren't left
> > scratching their heads as to why it works - or why if they remove the
> newline
> > in their own attempt the code suddenly breaks.
>
> Yeah, I am kind on the fence about it, but it is a nice feature,
> particulary for PL/pgSQL programs.  I added a mention of the string
> concatentation feature --- patch attached, and URL updated.
>
>
>
> The third option is to just embed a new line in the string itself.
>
> Execute Format(’...
> ...', tbl)
> USING val

True, but that just looks odd.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

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 <bruce(at)momjian(dot)us> 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 "​
> > ​
> > There are other places later in the docs" confused me since you made changes
> > before and after that specific section.
> >
> > Those examples need to be somewhere and it doesn't seem like a undesireable
> > enough setup that major reconstructive surgery is warranted to try and move
> > them elsewhere.
>
> Yes, agreed.

Patch applied.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +