Received: from chennai.transys (chennai.transys.net [12.33.140.105]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0E5GJB14862 for ; Mon, 14 Jan 2002 00:16:20 -0500 (EST) (envelope-from unnikrishnan.menon@chennai.transys.net) Received: from UNNIKRISHNAN ([192.168.10.233]) by chennai.transys with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2650.21) id CGH4YXK2; Mon, 14 Jan 2002 10:58:58 +0530 Message-ID: <003401c19cba$ebea5e60$e90aa8c0@UNNIKRISHNAN> From: "Unnikrishnan Menon" To: "Ross J. Reedstrom" , References: <008801c19c3f$d588ce70$e90aa8c0@UNNIKRISHNAN> <20020113161931.GB7025@rice.edu> Subject: Re: Problems with PL/PGSQL - functions Date: Mon, 14 Jan 2002 10:48:31 +0530 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200201/118 X-Sequence-Number: 6207 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" To: "Unnikrishnan Menon" Cc: 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