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
