Re: proposal: doc: simplify examples of dynamic SQL

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