Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
