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