Обсуждение: 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
От
"Franz Stuetzle"
Дата:
<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>
"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