pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsql error creating function
2+ messages / 2 participants
[nested] [flat]
* sql error creating function
@ 2006-08-11 00:33 TJ O'Donnell <tjo@acm.org>
0 siblings, 1 reply; 2+ messages in thread
From: TJ O'Donnell @ 2006-08-11 00:33 UTC (permalink / raw)
To: pgsql-sql
When I psql -f f.sql
I get the following error:
psql:f.sql:10: ERROR: relation "fragset" does not exist
CONTEXT: SQL function "fragments"
> cat f.sql
Create Or Replace Function fragments(character varying)
Returns setof character varying
As $$
Create Temporary Table fragset (smiles character varying);
Insert into fragset Values ('COCNC');
Insert into fragset Values ('COCNCc1ccccc1');
Select smiles from fragset;
$$ Language SQL;
But, if I paste into a running psql the commands:
Create Temporary Table fragset (smiles character varying);
Insert into fragset Values ('COCNC');
Insert into fragset Values ('COCNCc1ccccc1');
Select smiles from fragset;
it works fine.
What is wrong in the function definition?
I'm using 8.1.3
Thanks,
TJ O'Donnell
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: sql error creating function
@ 2006-08-11 12:48 Christopher Browne <cbbrowne@acm.org>
parent: TJ O'Donnell <tjo@acm.org>
0 siblings, 0 replies; 2+ messages in thread
From: Christopher Browne @ 2006-08-11 12:48 UTC (permalink / raw)
To: pgsql-sql
Quoth tjo@acm.org ("TJ O'Donnell"):
> When I psql -f f.sql
> I get the following error:
> psql:f.sql:10: ERROR: relation "fragset" does not exist
> CONTEXT: SQL function "fragments"
>
>> cat f.sql
> Create Or Replace Function fragments(character varying)
> Returns setof character varying
> As $$
>
> Create Temporary Table fragset (smiles character varying);
> Insert into fragset Values ('COCNC');
> Insert into fragset Values ('COCNCc1ccccc1');
> Select smiles from fragset;
>
> $$ Language SQL;
>
> But, if I paste into a running psql the commands:
>
> Create Temporary Table fragset (smiles character varying);
> Insert into fragset Values ('COCNC');
> Insert into fragset Values ('COCNCc1ccccc1');
> Select smiles from fragset;
>
> it works fine.
>
> What is wrong in the function definition?
> I'm using 8.1.3
Presumably it's that the query plan is evaluated based on the state of
the database *before* the function runs, at which point table
'fragset' does not exist. Creation of the temp table will work fine,
but the references to it don't, because when the query (to run the
whole thing) is planned, the table doesn't exist.
What you need to do is to generate the various queries on fragset as
dynamic queries, invoked via "execute." That will cause the planner
to run on each statement...
--
output = reverse("moc.liamg" "@" "enworbbc")
http://linuxdatabases.info/info/wp.html
"We believe Windows 95 is a walking antitrust violation"
-- Bryan Sparks
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-08-11 12:48 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-11 00:33 sql error creating function TJ O'Donnell <tjo@acm.org>
2006-08-11 12:48 ` Christopher Browne <cbbrowne@acm.org>
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