From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-sql@postgresql.org
Subject: Re: sql error creating function
Date: Fri, 11 Aug 2006 08:48:19 -0400
Message-ID: <87veoztp4c.fsf@wolfe.cbbrowne.com> (raw)
References: <44DBD05C.9020800@acm.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: sql error creating function
In-Reply-To: <87veoztp4c.fsf@wolfe.cbbrowne.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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