Reputation: 95
This could be because i'm tired, or that I'm new to postgres. However, I'm trying to use a temp table in a function and postgres is complaining that the "relation does not exist". Yet, if I take the body of my function, and execute it it works just fine. Below is a sample of the type of function I'm trying to create. Keeping in mind I've stripped out everything interesting such that it's close to a bare minimum to show my problem.
CREATE OR REPLACE FUNCTION dbo.somefunc() RETURNS void AS
$BODY$
CREATE TEMPORARY TABLE work_list
(
name text,
level smallint
);
insert into work_list
(name, level)
values
('someone', 25);
$BODY$
LANGUAGE sql VOLATILE;
The complaint I get is on the insert statement. The actual complaint is:
ERROR: relation "work_list" does not exist
Does postgres not support temp tables in functions? Or is there some syntax thing that I'm missing that the thing is choking on and it's giving me a bogus error?
Upvotes: 6
Views: 13124
Reputation: 1327
Temporary tables last throughout a session.
If your PostgreSQL client use connection pooling (connections are created lazily once a query is created), then it might give you a different connection (therefore different session) for each query.
Read how to reserve or isolate a connection from the pool in your client. If you want to make a transaction, follow your client's guide on making transactions so that it will use a reserved connection.
Upvotes: 0
Reputation: 659367
Postgres runs some simple checks on the function you are trying to create, and it finds (correctly) that the table work_list
does not (yet) exist. I see two options:
1. "Fake it till you make it"
Actually create the (temporary) table before you create the function. The temporary table will be gone at the end of the session, but once the function is created, you have passed this test for good.
Obviously, you'd have to drop that table before you run the function in the same session to avoid a conflict. Better, though: use CREATE TEMP TABLE IF NOT EXISTS
in your function (Postgres 9.1+). You may want to truncate the table if it already exists ...
However (see comments below), quoting the manual
The entire body of a SQL function is parsed before any of it is executed. While a SQL function can contain commands that alter the system catalogs (e.g.,
CREATE TABLE
), the effects of such commands will not be visible during parse analysis of later commands in the function. Thus, for example,CREATE TABLE foo (...); INSERT INTO foo VALUES(...);
will not work as desired if packaged up into a single SQL function, since foo won't exist yet when theINSERT
command is parsed. It's recommended to use PL/pgSQL instead of a SQL function in this type of situation.
Bold emphasis mine.
Checks are less thorough in plpgsql. If Postgres still complains (which it doesn't in this case), you can also execute SQL dynamically with EXECUTE
.
Aside: In many cases, there is a more performant solution without temp table around the corner ...
Upvotes: 5
Reputation: 19
combine the two statements. Create the temp table by doing a "select into" type syntax. In that way you can do it. CREATE TEMP TABLE SomeTable AS SELECT * FROM OtherTable ;
Upvotes: 1