Обсуждение: plpgsql question: select into multiple variables ?

Hi,



Postgres version 9.3.9


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.

In this simplified concept function I end up with a NULL for first or last weekend variable.


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
          WHERE month_of_year = (extract(MONTH FROM current_date))::int AND
              year_of_date = (extract(YEAR FROM current_date))::int AND
             day_of_week IN ( 'Sat','Sun');

 RETURN( COALESCE(last_weekend,'01-jun-2014'));

END
$$
LANGUAGE plpgsql volatile;


If I execute the same select logic from a psql shell I get the correct result.


(1 row)

ace_db=# ace_db=#  SELECT MIN(CAL_DATE),MAX(CAL_DATE) FROM sys.calendar cal
                                                                               WHERE cal.month_of_year = (extract(MONTH
FROMcurrent_date))::int AND
        cal.year_of_date = (extract(YEAR FROM current_date))::int AND
                                                    cal.day_of_week IN ( 'Sat','Sun'); 
    min     |    max
------------+------------
 2015-06-06 | 2015-06-28
(1 row)


If I simplify to a single variable it works. i.e


create or replace function sys.time_test ()
returns date as
$$
DECLARE
   first_weekend date;
   last_weekend date;
BEGIN

  SELECT MIN(CAL_DATE) INTO first_weekend::date FROM sys.calendar
          WHERE month_of_year = (extract(MONTH FROM current_date))::int AND
              year_of_date = (extract(YEAR FROM current_date))::int AND
             day_of_week IN ( 'Sat','Sun');

 RETURN( COALESCE(first_weekend,'01-jun-2014'));

END
$$
LANGUAGE plpgsql volatile;



I suppose I can adjust to write my actual function to have 2 selects; one for each variable.
However, I thought according to the documentation the targets could/must match the result columns for select into ?


Thoughts


Thanks


Dave Day

On 06/29/2015 12:07 PM, Day, David wrote:
> Hi,
>
>
>
> Postgres version 9.3.9
>
>
> 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.
>
> In this simplified concept function I end up with a NULL for first or last weekend variable.
>
>
> 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
>            WHERE month_of_year = (extract(MONTH FROM current_date))::int AND
>                year_of_date = (extract(YEAR FROM current_date))::int AND
>               day_of_week IN ( 'Sat','Sun');
>
>   RETURN( COALESCE(last_weekend,'01-jun-2014'));
>
> END
> $$
> LANGUAGE plpgsql volatile;

The ::date cast seem to be the problem. When I tried a version of the
function here with them I got the same output. Eliminating them got the
correct output. They are redundant as you already DECLAREd first_weekend
and last_weekend to be DATE type. So:

  SELECT MIN(CAL_DATE),MAX(CAL_DATE) INTO first_weekend, last_weekend .....
>
>
> If I execute the same select logic from a psql shell I get the correct result.
>
>
> (1 row)
>
> ace_db=# ace_db=#  SELECT MIN(CAL_DATE),MAX(CAL_DATE) FROM sys.calendar cal
                                                                                 WHERE cal.month_of_year =
(extract(MONTHFROM current_date))::int AND
                       cal.year_of_date = (extract(YEAR FROM current_date))::int AND
                                                                   cal.day_of_week IN ( 'Sat','Sun'); 
>      min     |    max
> ------------+------------
>   2015-06-06 | 2015-06-28
> (1 row)
>
>
> If I simplify to a single variable it works. i.e
>
>
> create or replace function sys.time_test ()
> returns date as
> $$
> DECLARE
>     first_weekend date;
>     last_weekend date;
> BEGIN
>
>    SELECT MIN(CAL_DATE) INTO first_weekend::date FROM sys.calendar
>            WHERE month_of_year = (extract(MONTH FROM current_date))::int AND
>                year_of_date = (extract(YEAR FROM current_date))::int AND
>               day_of_week IN ( 'Sat','Sun');
>
>   RETURN( COALESCE(first_weekend,'01-jun-2014'));
>
> END
> $$
> LANGUAGE plpgsql volatile;
>
>
>
> I suppose I can adjust to write my actual function to have 2 selects; one for each variable.
> However, I thought according to the documentation the targets could/must match the result columns for select into ?
>
>
> Thoughts
>
>
> Thanks
>
>
> Dave Day
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.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.

To make this noticeably better, we'd probably have to insist that
INTO come at the end of the SELECT list, 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.

            regards, tom lane

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Monday, June 29, 2015 4:03 PM
To: Day, David; pgsql-general@postgresql.org
Subject: Re: [GENERAL] plpgsql question: select into multiple variables ?

On 06/29/2015 12:07 PM, Day, David wrote:
> Hi,
>
>
>
> Postgres version 9.3.9
>
>
> 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.
>
> In this simplified concept function I end up with a NULL for first or last weekend variable.
>
>
> 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
>            WHERE month_of_year = (extract(MONTH FROM current_date))::int AND
>                year_of_date = (extract(YEAR FROM current_date))::int AND
>               day_of_week IN ( 'Sat','Sun');
>
>   RETURN( COALESCE(last_weekend,'01-jun-2014'));
>
> END
> $$
> LANGUAGE plpgsql volatile;

The ::date cast seem to be the problem. When I tried a version of the function here with them I got the same output.
Eliminatingthem got the correct output. They are redundant as you already DECLAREd first_weekend and last_weekend to be
DATEtype. So: 

  SELECT MIN(CAL_DATE),MAX(CAL_DATE) INTO first_weekend, last_weekend .....
>
>
> If I execute the same select logic from a psql shell I get the correct result.
>
>
> (1 row)
>
> ace_db=# ace_db=#  SELECT MIN(CAL_DATE),MAX(CAL_DATE) FROM sys.calendar cal
                                                                                 WHERE cal.month_of_year =
(extract(MONTHFROM current_date))::int AND
                       cal.year_of_date = (extract(YEAR FROM current_date))::int AND
                                                                   cal.day_of_week IN ( 'Sat','Sun'); 
>      min     |    max
> ------------+------------
>   2015-06-06 | 2015-06-28
> (1 row)
>
>
> If I simplify to a single variable it works. i.e
>
>
> create or replace function sys.time_test () returns date as $$ DECLARE
>     first_weekend date;
>     last_weekend date;
> BEGIN
>
>    SELECT MIN(CAL_DATE) INTO first_weekend::date FROM sys.calendar
>            WHERE month_of_year = (extract(MONTH FROM current_date))::int AND
>                year_of_date = (extract(YEAR FROM current_date))::int AND
>               day_of_week IN ( 'Sat','Sun');
>
>   RETURN( COALESCE(first_weekend,'01-jun-2014'));
>
> END
> $$
> LANGUAGE plpgsql volatile;
>
>
>
> I suppose I can adjust to write my actual function to have 2 selects; one for each variable.
> However, I thought according to the documentation the targets could/must match the result columns for select into ?
>
>
> Thoughts
>
>
> Thanks
>
>
> Dave Day
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com



I agree with your evaluation.
I originally had that, but in playing around with the function had added the casts with no benefit and seemingly no
harmeither. 
I later noted that I was comparing to 'SAT','SUN' rather then 'Sat','Sun' in my calendar table.
After repairing that I forgot to back out the date cast.

Although the cast was redundant as you pointed out. I am  not quite sure why it made it not work.
Nontheless,  I am happy to move on to other issues.


Thanks very much for your assistance.



Dave Day

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Jun 29, 2015 at 4:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> ... 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 ...

> ​Does it help to recognize the fact that "first_weekend::​date" is not a
> valid identifier name (because it is lacking double-quotes)?

No.  You're supposing that we *should* reject this case, which is not
true given the current rules.  As a counterexample consider

     SELECT INTO x - y FROM foo

which per current rules means

     SELECT - y INTO x FROM foo

The only real difference between this and the :: case is that :: doesn't
come in a prefix-operator form, but that's an awfully weak reed to hang
a cross-language syntax rule on.

>> 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?

No, I'm not.  See previous example.  To detect errors more completely,
we'd need a rule that what follows the INTO clause be "FROM" and nothing
else (well, maybe "GROUP BY" and some other cases, but in any case a fully
reserved word).  As things stand, to support INTO-someplace-else we have
to suppose that anything other than identifiers and commas is not part of
INTO but belongs to the SELECT expression list.  It's precisely the lack
of any clear delimiter between INTO's arguments and the main SELECT syntax
that is biting us, and as long as we allow INTO somewhere other than after
the SELECT expression list, we can't have a delimiter because of the
historical choice not to.

            regards, tom lane