pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Creating Tables in Functions 2+ messages / 2 participants [nested] [flat]
* Creating Tables in Functions @ 2000-05-19 14:28 Scott Wood <skwny@email.com> 2000-05-19 19:48 ` Re: Creating Tables in Functions Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 2+ messages in thread From: Scott Wood @ 2000-05-19 14:28 UTC (permalink / raw) To: pgsql-sql All, I am trying to create a function that will create a temporary table and fill that table with data. Here is my function definition below (much simplified for this discussion): 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 Of course if doesn't exist, I haven't created it yet! What am I doing wrong? I am using 7.0. Thanks in advance for any help. Scott skwny@email.com ----------------------------------------------- FREE! The World's Best Email Address @email.com Reserve your name now at http://www.email.com ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Creating Tables in Functions 2000-05-19 14:28 Creating Tables in Functions Scott Wood <skwny@email.com> @ 2000-05-19 19:48 ` Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2000-05-19 19:48 UTC (permalink / raw) To: Scott Wood <skwny@email.com>; +Cc: pgsql-sql Scott Wood <skwny@email.com> 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-05-19 19:48 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2000-05-19 14:28 Creating Tables in Functions Scott Wood <skwny@email.com> 2000-05-19 19:48 ` 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