Re: proposal: doc: simplify examples of dynamic SQL

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@momjian.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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +