Re: [GENERAL] plpgsql question: select into multiple variables ?
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:yasinsar...@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 mailto:d...@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
Re: [GENERAL] plpgsql question: select into multiple variables ?
"David G. Johnston" writes: > On Mon, Jun 29, 2015 at 4:27 PM, Tom Lane wrote: >> ... So what you wrote here is equivalent to >> >> SELECT MIN(CAL_DATE),MAX(CAL_DATE) ::date, last_weekend::date INTO >> first_weekend FROM sys.calendar ... > âDoes it help to recognize the fact that "first_weekend::âdate" is not a > valid identifier name (because it is lacking double-quotes)? No. You're supposing that we *should* reject this case, which is not true given the current rules. As a counterexample consider SELECT INTO x - y FROM foo which per current rules means SELECT - y INTO x FROM foo The only real difference between this and the :: case is that :: doesn't come in a prefix-operator form, but that's an awfully weak reed to hang a cross-language syntax rule on. >> To make this noticeably better, we'd probably have to insist that >> INTO come at the end of the SELECT list, > Are you missing a "not" here? No, I'm not. See previous example. To detect errors more completely, we'd need a rule that what follows the INTO clause be "FROM" and nothing else (well, maybe "GROUP BY" and some other cases, but in any case a fully reserved word). As things stand, to support INTO-someplace-else we have to suppose that anything other than identifiers and commas is not part of INTO but belongs to the SELECT expression list. It's precisely the lack of any clear delimiter between INTO's arguments and the main SELECT syntax that is biting us, and as long as we allow INTO somewhere other than after the SELECT expression list, we can't have a delimiter because of the historical choice not to. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql question: select into multiple variables ?
On Mon, Jun 29, 2015 at 4:27 PM, Tom Lane wrote: > Adrian Klaver writes: > > On 06/29/2015 12:07 PM, Day, David wrote: > >> 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. > >> > >> 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 ... > > > The ::date cast seem to be the problem. > > Indeed. Here's what's happening: the argument of INTO can basically only > be a list of variable names. (Well, they can be qualified field names, > but certainly not cast expressions.) And there's this messy legacy syntax > rule that says the INTO clause can be anywhere inside the SELECT list. > So what happens is the plpgsql parser reads "INTO first_weekend", notes > the next token is :: which can't be part of INTO, and drops back to > handling the rest of the input as SELECT text. So what you wrote here is > equivalent to > > SELECT MIN(CAL_DATE),MAX(CAL_DATE) ::date, last_weekend::date INTO > first_weekend FROM sys.calendar ... > > which accidentally looks like perfectly valid SELECT syntax. And I think > it doesn't complain about "too many output columns" either. So you end up > with no reported error and very confusing results. > Does it help to recognize the fact that "first_weekend::date" is not a valid identifier name (because it is lacking double-quotes)? It knows that "::" cannot be part of INTO but it is in the middle of reading the characters of an identifier and without quoting it cannot one of those either. Can that be made to take precedence and at least cause this specific case to fail? > To make this noticeably better, we'd probably have to insist that > INTO come at the end of the SELECT list, Are you missing a "not" here? "...insist that INTO not come at the end of the SELECT list"? It does seem any other location results in a syntax error - including in between the two select-list columns (i.e., MAX(...) INTO var1::date, var2::date MIN(...)) > which would break lots and > lots of existing client code ... so I'm not holding my breath. > > Moral of the story: being user-friendly by accepting sloppy syntax > is not an unalloyed win. > > From the documentation: """ SELECT select_expressions INTO [STRICT] target FROM ...; If a row or a variable list is used as target, the query's result columns must exactly match the structure of the target as to number and data types, or else a run-time error occurs. When a record variable is the target, it automatically configures itself to the row type of the query result columns. The INTO clause can appear almost anywhere in the SQL command. Customarily it is written either just before or just after the list of select_expressions in a SELECT command, or at the end of the command for other command types. It is recommended that you follow this convention in case the PL/pgSQL parser becomes stricter in future versions. """ I've never really liked the above "customarily" advice and do so less given this example. For all other statement types the INTO is the last clause written and while that may not be what experienced people default to doing it seems reasonable, safe, and consistent to suggest the same location for SELECT queries while noting that indeed its position just before or after the select list are common in the wild. The comment about becoming stricter should probably just be removed because, as noted, it ain't gonna happen. I'll admit that this all is not likely worth a great deal of effort given the lack of complains and the obviousness of the problem's manifestation. But it is the case that the lack of an error occurs in the recommended syntax form. David J.
Re: [GENERAL] plpgsql question: select into multiple variables ?
-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
Re: [GENERAL] plpgsql question: select into multiple variables ?
Adrian Klaver writes: > On 06/29/2015 12:07 PM, Day, David wrote: >> 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. >> >> 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 ... > The ::date cast seem to be the problem. Indeed. Here's what's happening: the argument of INTO can basically only be a list of variable names. (Well, they can be qualified field names, but certainly not cast expressions.) And there's this messy legacy syntax rule that says the INTO clause can be anywhere inside the SELECT list. So what happens is the plpgsql parser reads "INTO first_weekend", notes the next token is :: which can't be part of INTO, and drops back to handling the rest of the input as SELECT text. So what you wrote here is equivalent to SELECT MIN(CAL_DATE),MAX(CAL_DATE) ::date, last_weekend::date INTO first_weekend FROM sys.calendar ... which accidentally looks like perfectly valid SELECT syntax. And I think it doesn't complain about "too many output columns" either. So you end up with no reported error and very confusing results. To make this noticeably better, we'd probably have to insist that INTO come at the end of the SELECT list, which would break lots and lots of existing client code ... so I'm not holding my breath. Moral of the story: being user-friendly by accepting sloppy syntax is not an unalloyed win. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plpgsql question: select into multiple variables ?
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
