Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA33814 for ; Fri, 19 May 2000 15:49:04 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id PAA09495; Fri, 19 May 2000 15:48:46 -0400 (EDT) To: Scott Wood cc: pgsql-sql@postgresql.org Subject: Re: Creating Tables in Functions In-reply-to: <383872133.958746482309.JavaMail.root@web14.mail.com> References: <383872133.958746482309.JavaMail.root@web14.mail.com> Comments: In-reply-to Scott Wood message dated "Fri, 19 May 2000 10:28:02 -0400" Date: Fri, 19 May 2000 15:48:46 -0400 Message-ID: <9492.958765726@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200005/88 Scott Wood writes: > create function my_function() returns int4 as' > create temp table my_table(a integer); > insert into my_table values (1); > ' language 'sql'; > When I try to create this function, I always get the error: > ERROR: Relation 'my_table' does not exist The reason this fails is that the SQL function creation code tries to parse the function body (to check for errors). The "create" part parses OK, but then the "insert" command fails to parse because ... you got it ... my_table doesn't exist. You could maybe get around that by having some table named my_table exist at the time you create the function. However, if you are planning to create and delete tables named "my_table" multiple times in a single backend session I think it won't work anyway :-(. IIRC there is a cache for the results of parsing/planning an SQL function, so once a function has been executed once by a backend it will keep trying to access the same my_table that existed when it was first executed. (I might be confusing the way plpgsql and SQL functions are handled, though.) There has been talk about arranging to flush the cache when referenced tables are deleted or modified, but nothing's been done about it yet. This seems like a really weirdly designed arrangement anyhow. I suppose it might make more sense in the full context of what you were trying to do rather than this stripped-down example. Perhaps if you explain where you're trying to get to, we could give you advice about alternate ways of accomplishing the task. regards, tom lane