Re: plpgsql question: select into multiple variables ?

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