Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #120 > unrolled thread

ERROR: relation with OID 65748 does not exist

Back to article view | Back to comp.databases.postgresql

Contents

  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

#120 — ERROR: relation with OID 65748 does not exist

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;

[toc] | [next] | [standalone]

#121

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 

[toc] | [prev] | [next] | [standalone]

#122 — Re: Chris Leverkuehn wrote:You need dynamic SQL for the CREATE TABLE statement

Thanks Laurenz. That worked.  

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web