Re: error querying temp table in plpgsql function - Mailing list pgsql-general

From Richard Huxton
Subject Re: error querying temp table in plpgsql function
Date
Msg-id 419B57D3.80409@archonet.com
Whole thread Raw
In response to error querying temp table in plpgsql function  (Timothy Perrigo <tperrigo@wernervas.com>)
List pgsql-general
Timothy Perrigo wrote:
> (PostgreSQL 8.0 beta 4 on Mac OS X 10.3.6)
>
> I'm working on a function which creates and populates a temporary table,
> then returns the number of records it has inserted.  I'm getting an
> error, though, after successive invocations of the function (I can call
> it once successfully, but on the next call I get an error).  I've been
> able to reproduce the error with the following sample function:

> select count(*) into result from test;

> If, instead of executing the "select count(*)" directly, I use the
> EXECUTE command, then everything works.  Is this expected behavior?

Yes - the reference to table "test" will be compiled down to it's OID.
When you re-create the test table it will have a new OID and so you get
an error.

As you say, you need to use EXECUTE in cases like this with plpgsql. An
interpreted language (pltcl/plperl) should be OK in situations like
this, though at the cost of parsing the query each time the function is
called.

--
   Richard Huxton
   Archonet Ltd

pgsql-general by date:

Previous
From: Timothy Perrigo
Date:
Subject: error querying temp table in plpgsql function
Next
From: Timo Haberkern
Date:
Subject: Re: TSearch2: Problems with compound words and stop words