CODEWITHSUNDEEP
sqlpostgresqluser-defined-functionstemp-tables
OpenIdsAreDumb
OpenIdsAreDumb

Reputation: 95

postgres doesn't recognize temp table in function

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

Answers (3)

M Imam Pratama
M Imam Pratama

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

Erwin Brandstetter
Erwin Brandstetter

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 the INSERT command is parsed. It's recommended to use PL/pgSQL instead of a SQL function in this type of situation.

Bold emphasis mine.

2. Use PL/pgSQL instead

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

OcRob
OcRob

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

Related Questions