Re: proposal: doc: simplify examples of dynamic SQL

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.