Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!selfless.tophat.at!news.glorb.com!npeer02.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!post01.iad.highwinds-media.com!newsfe10.iad.POSTED!5788d997!not-for-mail
From: Chris Leverkuehn <chris.leverkuehn@tibra.com>
Subject: ERROR: relation with OID 65748 does not exist
Newsgroups: comp.databases.postgresql
Message-ID: <201151154528usenet@terrranews.com>
Lines: 35
X-Complaints-To: abuse@teranews.com
NNTP-Posting-Date: Wed, 11 May 2011 09:45:37 UTC
Organization: TeraNews.com
Date: Wed, 11 May 2011 09:45:37 GMT
Xref: x330-a1.tempe.blueboxinc.net comp.databases.postgresql:120

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;