Received: from ece.rice.edu (ece.rice.edu [128.42.4.34]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0DGJRB17358 for ; Sun, 13 Jan 2002 11:19:27 -0500 (EST) (envelope-from reedstrm@rice.edu) Received: from localhost (localhost [127.0.0.1]) by ece.rice.edu (Postfix) with ESMTP id 2AAA068A60; Sun, 13 Jan 2002 10:19:32 -0600 (CST) Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by ece.rice.edu (Postfix) with ESMTP id 70CB068A0E; Sun, 13 Jan 2002 10:19:31 -0600 (CST) Received: from reedstrm by wallace.ece.rice.edu with local (Exim 3.22 #1 (Debian)) id 16PnM3-0001qo-00; Sun, 13 Jan 2002 10:19:31 -0600 Date: Sun, 13 Jan 2002 10:19:31 -0600 From: "Ross J. Reedstrom" To: Unnikrishnan Menon Cc: pgsql-sql@postgresql.org Subject: Re: Problems with PL/PGSQL - functions Message-ID: <20020113161931.GB7025@rice.edu> Mail-Followup-To: "Ross J. Reedstrom" , Unnikrishnan Menon , pgsql-sql@postgresql.org References: <008801c19c3f$d588ce70$e90aa8c0@UNNIKRISHNAN> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <008801c19c3f$d588ce70$e90aa8c0@UNNIKRISHNAN> User-Agent: Mutt/1.3.25i X-Virus-Scanned: by AMaViS snapshot-20010714 X-Archive-Number: 200201/108 X-Sequence-Number: 6197 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