From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | "Day, David" <dday(at)redcom(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: plpgsql question: select into multiple variables ? |
Date: | 2015-06-29 20:02:48 |
Message-ID: | 5591A468.5050906@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(MONTH FROM 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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2015-06-29 20:13:03 | Re: serialization failure why? |
Previous Message | Joshua D. Drake | 2015-06-29 19:53:12 | Re: WAL archive "resend policy" |