pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Scott Wood <skwny@email.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Creating Tables in Functions
Date: Fri, 19 May 2000 15:48:46 -0400
Message-ID: <9492.958765726@sss.pgh.pa.us> (raw)
In-Reply-To: <383872133.958746482309.JavaMail.root@web14.mail.com>
References: <383872133.958746482309.JavaMail.root@web14.mail.com>

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



view thread (2+ messages)

Message-ID: <9492.958765726@sss.pgh.pa.us>
Permalink:  ../9492.958765726@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/9492.958765726@sss.pgh.pa.us

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, skwny@email.com
  Subject: Re: Creating Tables in Functions
  In-Reply-To: <9492.958765726@sss.pgh.pa.us>
  References: <383872133.958746482309.JavaMail.root@web14.mail.com> <9492.958765726@sss.pgh.pa.us>

* 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