I want to create a SQL function that looks roughly like this:
create function foo()
returns void
set search_path = 'pg_catalog, pg_temp'
as $$
create temporary table a (b int) on commit drop;
insert into a select generate_series(0, 10);
$$ language sql;
That is, I want to create a temporary table and then immediately use it in the same function, to fill it with data and then read it back. (In actual practice, the data is calculated from a complex join operation, and is then used to generate various reports. A CTE is not practical because the data is required for multiple operations.)
However, when I execute the statement above to create the function, I encounter this error:
SQL error [42P01]: ERROR: relation "a" does not exist
That is, postgres apparently refuses to compile the function because it references a table that doesn't exist. However, that is of course entirely the intention, as the table should be created in the very same function it is referenced in.
If I create the temporary table before I create the function, I can create and run the function just fine. However, the table is very much intended to live in pg_temp, ensuring that multiple concurrent sessions can use the foo function without seeing each other's data, and also ensuring that the data gets dropped when it is no longer needed. Besides, when I create the function foo by connecting with a new session, creating a, creating foo and then committing (dropping a), then in subsequent transaction foo fails on call with this error:
SQL error [42P01]: ERROR: relation "a" does not exist
at: SQL function "foo" during startup
Requiring each connecting session to create the a table themselves before calling foo works, but is obviously utterly silly (and exposes the function's implementation details to the caller).
How can I get this function to work in the way that I expect?
