Re: plpgsql question: select into multiple variables ?

On Mon, Jun 29, 2015 at 4:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
> > On 06/29/2015 12:07 PM, Day, David wrote:
> >> What is wrong with my usage of the plpgsql "select into" concept
> >> I have a function to look into a calendar table to find the first and
> >> Last weekend date of a month.
> >>
> >> create or replace function sys.time_test ()
> >> returns date as
> >> $$
> >> DECLARE
> >> first_weekend date;
> >> last_weekend date;
> >> BEGIN
> >>
> >> SELECT MIN(CAL_DATE),MAX(CAL_DATE) INTO first_weekend::date,
> last_weekend::date FROM sys.calendar ...
>
> > The ::date cast seem to be the problem.
>
> Indeed. Here's what's happening: the argument of INTO can basically only
> be a list of variable names. (Well, they can be qualified field names,
> but certainly not cast expressions.) And there's this messy legacy syntax
> rule that says the INTO clause can be anywhere inside the SELECT list.
> So what happens is the plpgsql parser reads "INTO first_weekend", notes
> the next token is :: which can't be part of INTO, and drops back to
> handling the rest of the input as SELECT text. So what you wrote here is
> equivalent to
>
> SELECT MIN(CAL_DATE),MAX(CAL_DATE) ::date, last_weekend::date INTO
> first_weekend FROM sys.calendar ...
>
> which accidentally looks like perfectly valid SELECT syntax. And I think
> it doesn't complain about "too many output columns" either. So you end up
> with no reported error and very confusing results.
>

​Does it help to recognize the fact that "first_weekend::​date" is not a
valid identifier name (because it is lacking double-quotes)? It knows that
"::" cannot be part of INTO but it is in the middle of reading the
characters of an identifier and without quoting it cannot one of those
either. Can that be made to take precedence and at least cause this
specific case to fail?

> To make this noticeably better, we'd probably have to insist that
> INTO come at the end of the SELECT list,

Are you missing a "not" here? "...insist that INTO not come at the end of
the SELECT list"? It does seem any other location results in a syntax
error - including in between the two select-list columns (i.e., MAX(...)
INTO var1::date, var2::date MIN(...))

> which would break lots and
> lots of existing client code ... so I'm not holding my breath.
>
> Moral of the story: being user-friendly by accepting sloppy syntax
> is not an unalloyed win.
>
>
​From the documentation:

​"""
SELECT select_expressions INTO [STRICT] target FROM ...;

If a row or a variable list is used as target, the query's result columns
must exactly match the structure of the target as to number and data types,
or else a run-time error occurs. When a record variable is the target, it
automatically configures itself to the row type of the query result columns.

The INTO clause can appear almost anywhere in the SQL command. Customarily
it is written either just before or just after the list of
select_expressions in a SELECT command, or at the end of the command for
other command types. It is recommended that you follow this convention in
case the PL/pgSQL parser becomes stricter in future versions.
​"""​

I've never really liked the above "customarily" advice and do so less given
this example. For all other statement types the INTO is the last clause
written and while that may not be what experienced people default to doing
it seems reasonable, safe, and consistent to suggest the same location for
SELECT queries while noting that indeed its position just before or after
the select list are common in the wild. The comment about becoming
stricter should probably just be removed because, as noted, it ain't gonna
happen.

I'll admit that this all is not likely worth a great deal of effort given
the lack of complains and the obviousness of the problem's manifestation.
But it is the case that the lack of an error occurs in the recommended
syntax form.

David J.