Postgre: 8.0.1 Create Table insde a function gives strange error = at execution time

From franz.stuetzle@gmx.net Sun Mar 27 12:17:40 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 50351539CB for ; Sun, 27 Mar 2005 13:17:40 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 96283-10 for ; Sun, 27 Mar 2005 12:17:37 +0000 (GMT) Received: from mail.gmx.net (mail.gmx.de [213.165.64.20]) by svr1.postgresql.org (Postfix) with SMTP id AFA9853A9E for ; Sun, 27 Mar 2005 13:17:35 +0100 (BST) Received: (qmail invoked by alias); 27 Mar 2005 12:17:37 -0000 Received: from p50806589.dip0.t-ipconnect.de (EHLO amilo1) [80.128.101.137] by mail.gmx.net (mp004) with SMTP; 27 Mar 2005 14:17:37 +0200 X-Authenticated: #23387921 From: "Franz Stuetzle" To: Subject: Postgre: 8.0.1 Create Table insde a function gives strange error at execution time Date: Sun, 27 Mar 2005 14:17:38 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0006_01C532D7.BB2B4620" X-Mailer: Microsoft Office Outlook, Build 11.0.6353 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527 Thread-Index: AcUyxvciSzLseb0HSfalJjgxO+b2dg== X-Y-GMX-Trusted: 0 Message-Id: <20050327121735.AFA9853A9E@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.618 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, HTML_MESSAGE, MSGID_FROM_MTA_ID, UPPERCASE_25_50 X-Spam-Level: ** X-Archive-Number: 200503/355 X-Sequence-Number: 20834 This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C532D7.BB2B4620 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Code within a function is like this: SELECT 1 INTO x FROM pg_tables where tablename = 'globals_pac_adressarten' and tableowner = user; IF (NOT FOUND) THEN CREATE TEMPORARY TABLE globals_pac_adressarten (INT_ALIAS_NAME VARCHAR(12) NOT NULL ,KOMMUNIKATION SMALLINT NOT NULL ,POSTALISCH SMALLINT NOT NULL ) WITHOUT OIDS ON COMMIT PRESERVE ROWS; END IF; When executing following errors are reported: 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 KONTEXT: PL/pgSQL function "padr_insert_address" line 142 at SQL statement SQL statement "SELECT padr_insert_address('pCursor','1','1','xxx','0')" PL/pgSQL function "test" line 6 at select into variables ZEILE 1: ...dressarten (INT_ALIAS_NAME VARCHAR(12) NOT NULL , $1 SMALLI... Would anybody know where those $1 and $2 come from? If I run the "CREATE" under psql it's working OK; Help appreciated.. Franz Stuetzle Schertlinstr. 11-144 D-86159 Augsburg ------=_NextPart_000_0006_01C532D7.BB2B4620 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Postgre: 8.0.1 Create Table insde a function gives strange error = at execution time

Code within a function is like = this:

SELECT = 1 =

INTO x

FROM = pg_tables

where tablename = =3D 'globals_pac_adressarten'

and = tableowner =3D user;

IF (NOT FOUND) = THEN

= CREATE TEMPORARY TABLE globals_pac_adressarten

&n= bsp; (INT_ALIAS_NAME VARCHAR(12) NOT NULL

&n= bsp; ,KOMMUNIKATION SMALLINT NOT = NULL

&n= bsp; ,POSTALISCH SMALLINT NOT = NULL

&n= bsp; )

&n= bsp; WITHOUT OIDS

&n= bsp; ON COMMIT PRESERVE ROWS;

END = IF;

When executing following errors are = reported:

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

KONTEXT: PL/pgSQL function "padr_insert_address" = line 142 at SQL statement

SQL statement "SELECT = padr_insert_address('pCursor','1','1','xxx','0')"

PL/pgSQL function "test" line 6 at select into = variables

ZEILE 1: ...dressarten (INT_ALIAS_NAME VARCHAR(12) NOT NULL = , $1 SMALLI...

Would anybody know where those $1 and $2 come = from?

If I run the "CREATE" under psql it's working = OK;

Help appreciated……

&n= bsp; &nb= sp; &nbs= p; = ; =

Franz = Stuetzle

Schertlinstr. 11-144

D-86159 Augsburg

------=_NextPart_000_0006_01C532D7.BB2B4620-- From tgl@sss.pgh.pa.us Sun Mar 27 16:01:50 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3D31D535C2 for ; Sun, 27 Mar 2005 17:01:50 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 48899-02 for ; Sun, 27 Mar 2005 16:01:44 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 698E653557 for ; Sun, 27 Mar 2005 17:01:44 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2RG1goY005600; Sun, 27 Mar 2005 11:01:42 -0500 (EST) To: "Franz Stuetzle" Cc: pgsql-sql@postgresql.org Subject: Re: Postgre: 8.0.1 Create Table insde a function gives strange error at execution time In-reply-to: <20050327121735.AFA9853A9E@svr1.postgresql.org> References: <20050327121735.AFA9853A9E@svr1.postgresql.org> Comments: In-reply-to "Franz Stuetzle" message dated "Sun, 27 Mar 2005 14:17:38 +0200" Date: Sun, 27 Mar 2005 11:01:42 -0500 Message-ID: <5599.1111939302@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/358 X-Sequence-Number: 20837 "Franz Stuetzle" 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