Re: proposal: doc: simplify examples of dynamic SQL

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.