Postgre: 8.0.1 Create Table insde a function gives strange error at execution time - Mailing list pgsql-sql
| From | Franz Stuetzle |
|---|---|
| Subject | Postgre: 8.0.1 Create Table insde a function gives strange error at execution time |
| Date | |
| Msg-id | 20050327121735.AFA9853A9E@svr1.postgresql.org Whole thread Raw |
| Responses |
Re: Postgre: 8.0.1 Create Table insde a function gives strange error at execution time
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-sql |
<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>