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