[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [GENERAL] plpgsql question: select into multiple variables ?
From: "Day, David" <dday () redcom ! com>
Date: 2015-06-30 12:05:58
Message-ID: 401084E5E73F4241A44F3C9E6FD7942801183DBD93 () exch-01
[Download RAW message or body]
[Attachment #2 (text/plain)]
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@googlemail.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@redcom.com<mailto: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<mailto:pgsql-general@postgresql.org>) To make changes \
to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[Attachment #3 (text/html)]
<html xmlns:v="urn:schemas-microsoft-com:vml" \
xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" \
xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type \
content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 12 \
(filtered medium)"><style><!-- /* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.hoenzb
{mso-style-name:hoenzb;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div \
class=WordSection1><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Hi \
Yari,<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Thanks for \
the response.<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>You did \
make the “simplified concept” function more rational.<o:p></o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><br>However, \
<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>This was \
kind of a non-sense function to demonstrate the problem I was having with the “select \
fields” and the “into variables”.<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>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.<o:p></o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Appreciate \
your thought and effort.<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Regards<o:p></o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Dave<o:p></o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><div \
style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'><p \
class=MsoNormal><b><span \
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span \
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> Yasin Sari \
[mailto:yasinsari81@googlemail.com] <br><b>Sent:</b> Tuesday, June 30, 2015 3:26 \
AM<br><b>To:</b> Day, David<br><b>Subject:</b> Re: [GENERAL] plpgsql question: select \
into multiple variables ?<o:p></o:p></span></p></div><p \
class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>Hi \
David,<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p \
class=MsoNormal>this works for me.<o:p></o:p></p></div><div><p \
class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>CREATE OR REPLACE \
FUNCTION sys.time_test (<o:p></o:p></p></div><div><p class=MsoNormal> out \
first_weekend date,<o:p></o:p></p></div><div><p class=MsoNormal> out \
last_weekend date<o:p></o:p></p></div><div><p \
class=MsoNormal>)<o:p></o:p></p></div><div><p class=MsoNormal>RETURNS SETOF record \
AS<o:p></o:p></p></div><div><p class=MsoNormal>$body$<o:p></o:p></p></div><div><p \
class=MsoNormal>BEGIN<o:p></o:p></p></div><div><p \
class=MsoNormal><o:p> </o:p></p></div><div><p \
class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal> SELECT \
COALESCE(<span style='font-size:9.5pt'>MIN(CAL_DATE)</span>,'01-jun-2014'),COALESCE(<span \
style='font-size:9.5pt'>MAX(CAL_DATE)</span>,'01-jun-2014')<o:p></o:p></p></div><div><p \
class=MsoNormal> into first_weekend,last_weekend<o:p></o:p></p></div><div><p \
class=MsoNormal> FROM <span \
style='font-size:9.5pt'>sys.calendar</span><o:p></o:p></p></div><p \
class=MsoNormal><span style='font-size:9.5pt'>WHERE month_of_year = (extract(MONTH \
FROM current_date))::int AND<br> \
year_of_date = (extract(YEAR FROM current_date))::int \
AND</span><o:p></o:p></p><div><p class=MsoNormal><span style='font-size:9.5pt'> \
day_of_week IN ( \
'Sat','Sun')</span>;<o:p></o:p></p></div><div><p class=MsoNormal>return \
next;<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p \
class=MsoNormal>END<o:p></o:p></p></div><div><p \
class=MsoNormal>$body$<o:p></o:p></p></div><div><p class=MsoNormal>LANGUAGE \
'plpgsql'<o:p></o:p></p></div><div><p \
class=MsoNormal>VOLATILE<o:p></o:p></p></div><div><p class=MsoNormal>CALLED ON NULL \
INPUT<o:p></o:p></p></div><div><p class=MsoNormal>SECURITY \
INVOKER<o:p></o:p></p></div><div><p class=MsoNormal>COST 100 ROWS \
1000;<o:p></o:p></p></div></div><div><p class=MsoNormal><o:p> </o:p></p><div><p \
class=MsoNormal>On Mon, Jun 29, 2015 at 10:07 PM, Day, David <<a \
href="mailto:dday@redcom.com" target="_blank">dday@redcom.com</a>> \
wrote:<o:p></o:p></p><p class=MsoNormal>Hi,<br><br><br><br>Postgres version \
9.3.9<br><br><br>What is wrong with my usage of the plpgsql "select \
into" concept<br>I have a function to look into a calendar table to find the \
first and<br>Last weekend date of a month.<br><br>In this simplified concept function \
I end up with a NULL for first or last weekend variable.<br><br><br>create or replace \
function sys.time_test ()<br>returns date as<br>$$<br>DECLARE<br> \
first_weekend date;<br> last_weekend date;<br>BEGIN<br><br> \
SELECT MIN(CAL_DATE),MAX(CAL_DATE) INTO first_weekend::date, last_weekend::date FROM \
sys.calendar<br> WHERE month_of_year = \
(extract(MONTH FROM current_date))::int AND<br> \
year_of_date = (extract(YEAR FROM current_date))::int AND<br> \
day_of_week IN ( \
'Sat','Sun');<br><br> RETURN( \
COALESCE(last_weekend,'01-jun-2014'));<br><br>END<br>$$<br>LANGUAGE plpgsql \
volatile;<br><br><br>If I execute the same select logic from a psql shell I get the \
correct result.<br><br><br>(1 row)<br><br>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');<br> min \
| max<br>------------+------------<br> 2015-06-06 | \
2015-06-28<br>(1 row)<br><br><br>If I simplify to a single variable it works. \
i.e<br><br><br>create or replace function sys.time_test ()<br>returns date \
as<br>$$<br>DECLARE<br> first_weekend date;<br> last_weekend \
date;<br>BEGIN<br><br> SELECT MIN(CAL_DATE) INTO first_weekend::date FROM \
sys.calendar<br> WHERE month_of_year = \
(extract(MONTH FROM current_date))::int AND<br> \
year_of_date = (extract(YEAR FROM current_date))::int AND<br> \
day_of_week IN ( \
'Sat','Sun');<br><br> RETURN( \
COALESCE(first_weekend,'01-jun-2014'));<br><br>END<br>$$<br>LANGUAGE plpgsql \
volatile;<br><br><br><br>I suppose I can adjust to write my actual function to have 2 \
selects; one for each variable.<br>However, I thought according to the documentation \
the targets could/must match the result columns for select into \
?<br><br><br>Thoughts<br><br><br>Thanks<br><br><br>Dave Day<br><span \
style='color:#888888'><br><br><br><br><span class=hoenzb>--</span><br><span \
class=hoenzb>Sent via pgsql-general mailing list (<a \
href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a>)</span><br><span \
class=hoenzb>To make changes to your subscription:</span><br><span class=hoenzb><a \
href="http://www.postgresql.org/mailpref/pgsql-general" \
target="_blank">http://www.postgresql.org/mailpref/pgsql-general</a></span></span><o:p></o:p></p></div><p \
class=MsoNormal><o:p> </o:p></p></div></div></body></html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic