Thread: Problems with temp table and PL

Problems with temp table and PL

From

Martin Marques

Date:

21 February 2007, 20:17:58

I create a temp table inside a plpgsql function, which is drop just before
ending (the function).

My problem is that if I execute the same function again (in the same
session) I get an error when trying to insert data into it (looks like the
session has an old reference of the table):

=> SELECT actualizacionAnualProximoHabil(2008);
ERROR:  relation with OID 9668312 does not exist
CONTEXT:  SQL statement "INSERT INTO dias_semana VALUES ( $1 )"
PL/pgSQL function "actualizacionanualproximohabil" line 9 at SQL statement

As I said, the first execution works OK, but from then on it gives this
error, until I close the session and open it again.

Any ideas?

-- 21:50:04 up 2 days,  9:07,  0 users,  load average: 0.92, 0.37, 0.18
---------------------------------------------------------
Lic. Martín Marqués         |   SELECT 'mmarques' ||
Centro de Telemática        |       '@' || 'unl.edu.ar';
Universidad Nacional        |   DBA, Programador,    del Litoral             |   Administrador
---------------------------------------------------------

Re: Problems with temp table and PL

From

Andrew Sullivan

Date:

21 February 2007, 20:35:03

On Wed, Feb 21, 2007 at 06:17:50PM -0300, Martin Marques wrote:
> My problem is that if I execute the same function again (in the same 
> session) I get an error when trying to insert data into it (looks like the 
> session has an old reference of the table):

You have to use EXECUTE for this.  See the docs.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what 
you told them to.  That actually seems sort of quaint now.    --J.D. Baldwin

Re: Problems with temp table and PL

From

"A. Kretschmer"

Date:

21 February 2007, 20:37:14

am  Wed, dem 21.02.2007, um 18:17:50 -0300 mailte Martin Marques folgendes:
> I create a temp table inside a plpgsql function, which is drop just before 
> ending (the function).
> 
> My problem is that if I execute the same function again (in the same 
> session) I get an error when trying to insert data into it (looks like the 
> session has an old reference of the table):

Right. Use EXECUTE for DDL-commands.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net