Re: plpgsql question: select into multiple variables ?

Hi David,

this works for me.

CREATE OR REPLACE FUNCTION sys.time_test (

  out first_weekend date,

  out last_weekend date

)

RETURNS SETOF record AS

$body$

BEGIN

  SELECT COALESCE(MIN(CAL_DATE),'01-jun-2014'),COALESCE(MAX(CAL_DATE),'01-jun-2014')

  into first_weekend,last_weekend

  FROM sys.calendar

             day_of_week IN ( 'Sat','Sun');

return next;

END

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100 ROWS 1000;

On Mon, Jun 29, 2015 at 10:07 PM, Day, David <dday@redcom.com> 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;


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




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general