Thread: Problems with PL/PGSQL - functions

On Sun, Jan 13, 2002 at 08:07:27PM +0530, Unnikrishnan Menon wrote:
> Hi,
> 
> I have a couple of problems :
> 
> 1. I create a temporary table using the following method within a function:
> 
>     query := ''Create table S_'' || nextval(sequence) || '' ( abc integer, xyz varchar)'';
>     Execute query;
>     This query does create a table. But when I'am unable to access the records in the table. I use the following
methods:
>     
>     a. Select xyz into variable from S_''|| nextval(sequence)||'' where abc = (some counter value) ;
>     b. query1 := '' Select xyz as variable from S_''|| nextval(sequence)||'' where abc = (some counter value) '' ;
>         Execute query1;

Right, because each one increments the sequence. Try substituting currentval().
Oh, and you might want to create the table as a temp table, in a transaction,
so it'll go aeway automatically for you at the end of the transaction.

Ross

"Unnikrishnan Menon" <unnikrishnan.menon@chennai.transys.net> writes:
>     b. query1 :=3D '' Select xyz as variable from S_''|| nextval(sequence)|=
> |'' where abc =3D (some counter value) '' ;
>         Execute query1;

You can't use EXECUTE for a SELECT (no way to retrieve the result) nor
for a SELECT INTO (annoying implementation restriction that should be
removed someday).  The only way to get a result from a
dynamically-constructed select statement is to use FOR ... EXECUTE.

You do need to use EXECUTEs with any query involving a dynamically
generated table name, so you'll just have to grin and bear it.

But --- does the table actually have to be handled that way?  If you
need exactly one such temp table per session, you could use "CREATE TEMP
TABLE" and give the table a fixed name.  Then you could skip all the BS
with EXECUTE.  Note however that it has to be per session; dropping and
recreating the table mid-session won't work since already-cached query
plans in your plpgsql functions will still point to the old temp table.
        regards, tom lane

Hi Ross,

Iam sorry. when I wrote nextval(sequence) in the Select query I actually
meant the same table that I had created before. The question is that the
queries do not execute. They give errors in the first one and in the second
it gives a Not yet implmented error if i use Select into. The select as
query executes but does not return the value required in a variable.

Any idea on the second question on constraints :

What command or table gives the details of the constraints created other
than check constraints.

Thanx
Unni

----- Original Message -----
From: "Ross J. Reedstrom" <reedstrm@rice.edu>
To: "Unnikrishnan Menon" <unnikrishnan.menon@chennai.transys.net>
Cc: <pgsql-sql@postgresql.org>
Sent: Sunday, January 13, 2002 9:49 PM
Subject: Re: [SQL] Problems with PL/PGSQL - functions


> On Sun, Jan 13, 2002 at 08:07:27PM +0530, Unnikrishnan Menon wrote:
> > Hi,
> >
> > I have a couple of problems :
> >
> > 1. I create a temporary table using the following method within a
function:
> >
> >     query := ''Create table S_'' || nextval(sequence) || '' ( abc
integer, xyz varchar)'';
> >     Execute query;
> >     This query does create a table. But when I'am unable to access the
records in the table. I use the following methods:
> >
> >     a. Select xyz into variable from S_''|| nextval(sequence)||'' where
abc = (some counter value) ;
> >     b. query1 := '' Select xyz as variable from S_''||
nextval(sequence)||'' where abc = (some counter value) '' ;
> >         Execute query1;
>
> Right, because each one increments the sequence. Try substituting
currentval().
> Oh, and you might want to create the table as a temp table, in a
transaction,
> so it'll go aeway automatically for you at the end of the transaction.
>
> Ross