-----Original Message----- From: Adrian Klaver [mailto:adrian.kla...@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. 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.kla...@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 harm either. 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
