Groups > comp.databases.postgresql > #121

Re: ERROR: relation with OID 65748 does not exist

Path	csiph.com!x330-a1.tempe.blueboxinc.net!newsfeed.hal-mli.net!feeder1.hal-mli.net!weretis.net!feeder4.news.weretis.net!newsfeed.utanet.at!newscore.univie.ac.at!aconews-feed.univie.ac.at!aconews.univie.ac.at!not-for-mail
From	"Laurenz Albe" <invite@spam.to.invalid>
Newsgroups	comp.databases.postgresql
References	<201151154528usenet@terrranews.com>
Subject	Re: ERROR: relation with OID 65748 does not exist
Date	Wed, 11 May 2011 16:54:51 +0200
X-Priority	3
X-MSMail-Priority	Normal
X-Newsreader	Microsoft Outlook Express 6.00.2900.5931
X-RFC2646	Format=Flowed; Original
X-MimeOLE	Produced By Microsoft MimeOLE V6.00.2900.6090
Organization	dienste.wien.at ISP
Message-ID	<1305125713.432337@proxy.dienste.wien.at> (permalink)
X-Cache	nntpcache 2.3.3 (see http://www.nntpcache.org/)
Lines	41
NNTP-Posting-Host	141.203.254.23
X-Trace	1305125715 aconews.univie.ac.at 34944 141.203.254.23
X-Complaints-To	abuse@univie.ac.at
Xref	x330-a1.tempe.blueboxinc.net comp.databases.postgresql:121

Show key headers only | View raw

Chris Leverkuehn wrote:
> 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.

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

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

ERROR: relation with OID 65748 does not exist Chris Leverkuehn <chris.leverkuehn@tibra.com> - 2011-05-11 09:45 +0000
  Re: ERROR: relation with OID 65748 does not exist "Laurenz Albe" <invite@spam.to.invalid> - 2011-05-11 16:54 +0200
    Re: Chris Leverkuehn wrote:You need dynamic SQL for the CREATE TABLE statement Chris Leverkuehn <chris.leverkuehn@tibra.com> - 2011-05-11 23:59 +0000

csiph-web