From: | "Day, David" <dday(at)redcom(dot)com> |
---|---|
To: | Yasin Sari <yasinsari81(at)googlemail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: plpgsql question: select into multiple variables ? |
Date: | 2015-06-30 12:05:58 |
Message-ID: | 401084E5E73F4241A44F3C9E6FD7942801183DBD93@exch-01 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi Yari,
Thanks for the response.
You did make the “simplified concept” function more rational.
However,
This was kind of a non-sense function to demonstrate the problem I was having with the “select fields” and the “into variables”.
As pointed out by Adrian Klaver and Tom Lane, the real problem was in casts that I was using were confusing the parser and were un-necessary.
Appreciate your thought and effort.
Regards
Dave
From: Yasin Sari [mailto:yasinsari81(at)googlemail(dot)com]
Sent: Tuesday, June 30, 2015 3:26 AM
To: Day, David
Subject: Re: [GENERAL] 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
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 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(at)redcom(dot)com<mailto:dday(at)redcom(dot)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(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
From | Date | Subject | |
---|---|---|---|
Next Message | Lukasz Wrobel | 2015-06-30 12:57:05 | very slow queries and ineffective vacuum |
Previous Message | Bráulio Bhavamitra | 2015-06-30 11:39:47 | Re: Feature request: fsync and commit_delay options per database |