From: Christopher Browne <cbbrowne@acm.org>
Subject: Re: sql error creating function
Date: Fri, 11 Aug 2006 08:48:19 -0400
Organization: cbbrowne Computing Inc
Lines: 44
Message-ID: <87veoztp4c.fsf@wolfe.cbbrowne.com>
References: <44DBD05C.9020800@acm.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.19 (linux)
Cancel-Lock: sha1:8Cl8H9I/J8xBIfD+ycLlhz21soE=
To: pgsql-sql@postgresql.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