Re: Creating Tables in Functions

Scott Wood <skwny(at)email(dot)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