Hi,

I have been unable to understand any of solutions given for this problem. I have a function which creates/use temp tables. On the first query to the function, it works great, but then on the second I get the error:

ERROR: relation with OID ***** does not exist

Many posts discuss that the workaround is to use EXECUTE. I have been unable to figure out how to use this to replace the code I currently have. I tried using PREPARE and EXECUTE statements in the following manner but got the same problem (presumably because the prepared statements are cached.

PREPARE temp_query(long) as
SELECT hdr.id, hdr.source, SUM(data.volume) as volume, data.related_security_name as security
FROM recs.tbl_transaction_header hdr
INNER JOIN recs.tbl_transactions data
ON hdr.id = data.id
WHERE hdr.id = icpty1_id
GROUP BY hdr.id, hdr.source,security;

CREATE TEMP TABLE tbl_cpty1_transactions_sums ON COMMIT DROP AS
EXECUTE temp_query(1);
DEALLOCATE temp_query;


Can someone provide some sample code on how to create temp table (or to solve my problem). I'm out of ideas.

The below is how I currently create my temp tables:

CREATE TEMP TABLE tbl_cpty1_transactions_sums ON COMMIT DROP AS
SELECT hdr.id, hdr.source, SUM(data.volume) as volume, data.related_security_name as security
FROM recs.tbl_transaction_header hdr
INNER JOIN recs.tbl_transactions data
ON hdr.id = data.id
WHERE hdr.id = icpty1_id
GROUP BY hdr.id, hdr.source,security;

Chris Leverkuehn wrote:
You need dynamic SQL for the CREATE TABLE statement itself, not a prepared
statement for the query that fills it.
You are confusing the SQL statement EXECUTE with the PL/pgSQL statement EXECUTE.

Something like:

create_stmt := 'CREATE TEMP TABLE ... AS SELECT ...';
EXECUTE create_stmt;

Of course any other reference to the temporary table in your
function will have to be in dynamic SQL as well.

Yours,
Laurenz Albe