pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgre: 8.0.1 Create Table insde a function gives strange error at execution time
2+ messages / 2 participants
[nested] [flat]
* Postgre: 8.0.1 Create Table insde a function gives strange error at execution time
@ 2005-03-27 12:17 Franz Stuetzle <franz.stuetzle@gmx.net>
0 siblings, 1 reply; 2+ messages in thread
From: Franz Stuetzle @ 2005-03-27 12:17 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Postgre: 8.0.1 Create Table insde a function gives strange error at execution time
@ 2005-03-27 16:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Franz Stuetzle <franz.stuetzle@gmx.net>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2005-03-27 16:01 UTC (permalink / raw)
To: Franz Stuetzle <franz.stuetzle@gmx.net>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-03-27 16:01 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-27 12:17 Postgre: 8.0.1 Create Table insde a function gives strange error at execution time Franz Stuetzle <franz.stuetzle@gmx.net>
2005-03-27 16:01 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox