Thread: Postgre: 8.0.1 Create Table insde a function gives strange error at execution time
Postgre: 8.0.1 Create Table insde a function gives strange error at execution time
From
"Franz Stuetzle"
Date:
<p align="LEFT"><span lang="en-gb"><font face="Courier New" size="2">Code within a</font></span><span
lang="de"></span><spanlang="en-gb"> <font face="Courier New" size="2">function</font></span><span
lang="de"></span><spanlang="en-gb"><font face="Courier New" size="2"> is like this:</font></span><p align="LEFT"><span
lang="de"><fontface="Courier New" size="2"> SELECT 1 </font></span><span lang="de"> </span><p
align="LEFT"><spanlang="de"><font face="Courier New" size="2"> </font></span><span lang="de"></span><span
lang="en-gb"><font face="Courier New" size="2">INTO x</font></span><p align="LEFT"><span lang="en-gb"><font
face="CourierNew" size="2"> FROM pg_tables</font></span><p align="LEFT"><span lang="en-gb"><font
face="CourierNew" size="2"> where tablename = 'globals_pac_adressarten'</font></span><p align="LEFT"><span
lang="en-gb"><fontface="Courier New" size="2"> and tableowner = user;</font></span><p align="LEFT"><span
lang="en-gb"><fontface="Courier New" size="2"> IF (NOT FOUND) THEN</font></span><p align="LEFT"><span
lang="en-gb"><fontface="Courier New" size="2"> CREATE TEMPORARY TABLE
globals_pac_adressarten</font></span><palign="LEFT"><span lang="en-gb"><font face="Courier New" size="2">
(INT_ALIAS_NAMEVARCHAR(12) NOT NULL</font></span><p align="LEFT"><span lang="en-gb"><font face="Courier New"
size="2"> ,KOMMUNIKATION SMALLINT NOT NULL</font></span><p align="LEFT"><span lang="en-gb"><font
face="CourierNew" size="2"> ,POSTALISCH SMALLINT NOT NULL</font></span><p align="LEFT"><span
lang="en-gb"><fontface="Courier New" size="2"> )</font></span><p align="LEFT"><span lang="en-gb"><font
face="CourierNew" size="2"> WITHOUT OIDS</font></span><p align="LEFT"><span lang="en-gb"><font
face="CourierNew" size="2"> ON COMMIT PRESERVE ROWS;</font></span><span lang="de"></span><span
lang="en-gb"></span><palign="LEFT"><span lang="en-gb"><font face="Courier New" size="2"> END
IF;</font></span><spanlang="de"></span><span lang="en-gb"></span><p align="LEFT"><span lang="en-gb"><font face="Courier
New"size="2">When executing</font></span><span lang="de"></span><span lang="en-gb"> <font face="Courier New"
size="2">followingerrors are reported:</font></span><p align="LEFT"><span lang="en-gb"><font face="Courier New"
size="2">ERROR: syntax error at or near "$1" bei Zeichen 87</font></span><p align="LEFT"><span lang="en-gb"><font
face="CourierNew" size="2">ANFRAGE: CREATE TEMPORARY TABLE globals_pac_adressarten (INT_ALIAS_NAME VARCHAR(12) NOT
NULL,</font></span><span lang="de"><b></b></span><b><span lang="en-gb"> <font face="Courier New"
size="2">$1</font></span></b><spanlang="de"></span><span lang="en-gb"><font face="Courier New" size="2"> SMALLINT NOT
NULL,</font></span><p align="LEFT"><b><span lang="en-gb"><font face="Courier New" size="2">$2</font></span></b><span
lang="de"></span><spanlang="en-gb"><font face="Courier New" size="2"> SMALLINT NOT NULL ) WITHOUT OIDS ON COMMIT
PRESERVEROWS</font></span><p align="LEFT"><span lang="en-gb"><font face="Courier New" size="2">KONTEXT: PL/pgSQL
function"padr_insert_address" line 142 at SQL statement</font></span><p align="LEFT"><span lang="en-gb"><font
face="CourierNew" size="2">SQL statement "SELECT padr_insert_address('pCursor','1','1','xxx','0')"</font></span><p
align="LEFT"><spanlang="en-gb"><font face="Courier New" size="2">PL/pgSQL function "test" line 6 at select into
variables</font></span><palign="LEFT"><span lang="de"><font face="Courier New" size="2">ZEILE 1: ...dressarten
(INT_ALIAS_NAMEVARCHAR(12) NOT NULL ,</font></span><span lang="de"><b> <font face="Courier New"
size="2">$1</font></b></span><spanlang="de"><font face="Courier New" size="2"> SMALLI...</font></span><span
lang="de"></span><palign="LEFT"><span lang="en-gb"><font face="Courier New" size="2">Would anybody know where those $1
and$2 come from?</font></span><span lang="de"></span><span lang="en-gb"></span><p align="LEFT"><span lang="en-gb"><font
face="CourierNew" size="2">If I run the "CREATE" under psql it's working OK;</font></span><span lang="de"></span><span
lang="en-gb"></span><palign="LEFT"><span lang="en-gb"><font face="Courier New" size="2">Help
appreciated</font></span><spanlang="de"></span><span lang="en-gb"><font face="Courier New"
size="2">……</font></span><spanlang="de"></span><span lang="en-gb"></span><p align="LEFT"><span lang="en-gb"><font
face="CourierNew" size="2"> </font></span><span lang="de">
</span><palign="LEFT"><span lang="de-de"></span><a name=""><span lang="de-de"><font face="Courier New" size="2">Franz
Stuetzle</font></span></a><palign="LEFT"><span lang="de-de"><font face="Courier New" size="2">Schertlinstr.
11-144</font></span><palign="LEFT"><span lang="de-de"><font face="Courier New" size="2">D-86159
Augsburg</font></span><palign="LEFT"><span lang="de"></span>
Re: Postgre: 8.0.1 Create Table insde a function gives strange error at execution time
From
Tom Lane
Date:
"Franz Stuetzle" <franz.stuetzle@gmx.net> writes:
> ERROR: syntax error at or near "$1" bei Zeichen 87
> ANFRAGE: CREATE TEMPORARY TABLE globals_pac_adressarten (INT_ALIAS_NAME
> VARCHAR(12) NOT NULL , $1 SMALLINT NOT NULL ,
> $2 SMALLINT NOT NULL ) WITHOUT OIDS ON COMMIT PRESERVE ROWS
> Would anybody know where those $1 and $2 come from?
Undoubtedly they are from plpgsql variable substitution. As a general
rule, variables in a plpgsql function should never be named the same as
any table or field name that you need to access in that function,
because plpgsql isn't smart enough to tell whether it ought to
substitute its variable for a reference or not. It will always do so,
even in cases where there arguably might be a way for it to tell that
it shouldn't (and there are cases where it simply couldn't tell, anyway).
regards, tom lane