Thread: Why not working under 8.3
Following lines produce an error message under 8.3. - but not under 8.1.
Why?
CREATE OR REPLACE FUNCTION dodi() RETURNS boolean AS
$$
BEGIN
SELECT 'abc' INTO TEMPORARY foonana;
RETURN FALSE;
END;
$$
LANGUAGE 'plpgsql';
FEHLER: syntax error at "TEMPORARY"
DETAIL: Expected record variable, row variable, or list of scalar variables following INTO.
CONTEXT: compile of PL/pgSQL function "dodi" near line 2
********** Fehler **********
FEHLER: syntax error at "TEMPORARY"
SQL Status:42601
Detail:Expected record variable, row variable, or list of scalar variables following INTO.
Kontext:compile of PL/pgSQL function "dodi" near line 2
----- "Andreas Moeller" <a.moeller@teia.de> wrote: > Following lines produce an error message under 8.3. - but not under > 8.1. > Why? > > CREATE OR REPLACE FUNCTION dodi() RETURNS boolean AS > $$ > BEGIN > SELECT 'abc' INTO TEMPORARY foonana; > RETURN FALSE; > END; > $$ > LANGUAGE 'plpgsql'; > > > FEHLER: syntax error at "TEMPORARY" > DETAIL: Expected record variable, row variable, or list of scalar > variables following INTO. > CONTEXT: compile of PL/pgSQL function "dodi" near line 2 > > ********** Fehler ********** > > FEHLER: syntax error at "TEMPORARY" > SQL Status:42601 > Detail:Expected record variable, row variable, or list of scalar > variables following INTO. > Kontext:compile of PL/pgSQL function "dodi" near line 2 Are you sure this worked in 8.1? SELECT INTO in plpgsql needs to point to a variable as noted by the error message. You needto declare a variable.
----- "Adrian Klaver" <aklaver@comcast.net> wrote: > ----- "Andreas Moeller" <a.moeller@teia.de> wrote: > > > Following lines produce an error message under 8.3. - but not under > > 8.1. > > Why? > > > > CREATE OR REPLACE FUNCTION dodi() RETURNS boolean AS > > $$ > > BEGIN > > SELECT 'abc' INTO TEMPORARY foonana; > > RETURN FALSE; > > END; > > $$ > > LANGUAGE 'plpgsql'; > > > > > > FEHLER: syntax error at "TEMPORARY" > > DETAIL: Expected record variable, row variable, or list of scalar > > variables following INTO. > > CONTEXT: compile of PL/pgSQL function "dodi" near line 2 > > > > ********** Fehler ********** > > > > FEHLER: syntax error at "TEMPORARY" > > SQL Status:42601 > > Detail:Expected record variable, row variable, or list of scalar > > variables following INTO. > > Kontext:compile of PL/pgSQL function "dodi" near line 2 > > Are you sure this worked in 8.1? SELECT INTO in plpgsql needs to point > to a variable as noted by the error message. You need to declare a > variable. > > -- Out of curiosity I went back to 8.0 and ran the function and got: production=> SELECT dodi(); WARNING: column "?column?" has type "unknown" DETAIL: Proceeding with relation creation anyway. CONTEXT: SQL statement "SELECT 'abc' INTO TEMPORARY foonana" PL/pgSQL function "dodi" line 2 at SQL statement dodi ------ f (1 row) Seems it ignores the unknown type and returns the False value. Type casting has been tightened up in 8.3, so this qualifiesas an error now. Adrian Klaver aklaver@comcast.net
Adrian Klaver <aklaver@comcast.net> writes: > Out of curiosity I went back to 8.0 and ran the function and got: > production=> SELECT dodi(); > WARNING: column "?column?" has type "unknown" > DETAIL: Proceeding with relation creation anyway. Pre-8.2 plpgsql was willing to treat INTO as not special if the word right after it was not any known plpgsql variable, but this was never exactly a good thing to rely on. Per the recommendation in the docs, you should be using CREATE TABLE AS rather than SELECT INTO if you want to get at the non-plpgsql behavior of SELECT INTO. regards, tom lane
On Wed, Mar 4, 2009 at 11:05 AM, Adrian Klaver <aklaver@comcast.net> wrote: > > ----- "Andreas Moeller" <a.moeller@teia.de> wrote: > >> Following lines produce an error message under 8.3. - but not under >> 8.1. >> Why? >> >> CREATE OR REPLACE FUNCTION dodi() RETURNS boolean AS >> $$ >> BEGIN >> SELECT 'abc' INTO TEMPORARY foonana; >> RETURN FALSE; >> END; >> $$ >> LANGUAGE 'plpgsql'; >> >> >> FEHLER: syntax error at "TEMPORARY" >> DETAIL: Expected record variable, row variable, or list of scalar >> variables following INTO. >> CONTEXT: compile of PL/pgSQL function "dodi" near line 2 >> >> ********** Fehler ********** >> >> FEHLER: syntax error at "TEMPORARY" >> SQL Status:42601 >> Detail:Expected record variable, row variable, or list of scalar >> variables following INTO. >> Kontext:compile of PL/pgSQL function "dodi" near line 2 > > Are you sure this worked in 8.1? SELECT INTO in plpgsql needs to point to a variable as noted by the error message. Youneed to declare a variable. looks like ambiguity between plpgsql 'select into' and the regular sql 'select into' method which works like 'create table as'. I vaguely remember something about this changing. In any event. the OP is crazy to write it like that, 'create [temp] table as' is preferred. merlin