From unnikrishnan.menon@chennai.transys.net Sun Jan 13 14:35:16 2002 Received: from chennai.transys (chennai.transys.net [12.33.140.105]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0DEZFB07254 for ; Sun, 13 Jan 2002 09:35:16 -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 CGH4YW2S; Sun, 13 Jan 2002 20:17:53 +0530 Message-ID: <008801c19c3f$d588ce70$e90aa8c0@UNNIKRISHNAN> From: "Unnikrishnan Menon" To: Subject: Problems with PL/PGSQL - functions Date: Sun, 13 Jan 2002 20:07:27 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0085_01C19C6D.EC299140" 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/107 X-Sequence-Number: 6196 This is a multi-part message in MIME format. ------=_NextPart_000_0085_01C19C6D.EC299140 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I have a couple of problems : 1. I create a temporary table using the following method within a function: query :=3D ''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 reco= rds in the table. I use the following methods: =20=20=20=20 a. Select xyz into variable from S_''|| nextval(sequence)||'' where abc= =3D (some counter value) ; b. query1 :=3D '' Select xyz as variable from S_''|| nextval(sequence)|= |'' where abc =3D (some counter value) '' ; Execute query1; Both the queries do not give me the desired results. Could anyone sugge= st a way to get me the values. 2. What command or table gives the details of the constraints created other= than check constraints. 3. I'am using version 7.1.3. of PostgreSQL. Any idea whether any of the upc= oming versions supports the use of Execute for dynamic queries of Create ta= ble as... Select. Any help would be appreciated. Thanx in advance Unni ------=_NextPart_000_0085_01C19C6D.EC299140 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
 
I have a couple of problems :<= /DIV>
 
1. I create a temporary table using the fo= llowing=20 method within a function:
 
    query :=3D ''Create tab= le S_'' ||=20 nextval(sequence) || '' ( abc integer, xyz varchar)'';
    Execute query;
    This query does create = a table.=20 But when I'am unable to access the records in the table. I use the followin= g=20 methods:
   
    a. Select xyz into vari= able from=20 S_''|| nextval(sequence)||'' where abc =3D (some counter value) ;
    b. query1 :=3D '' Selec= t xyz as=20 variable from S_''|| nextval(sequence)||'' where abc =3D (some counter valu= e) ''=20 ;
        Exec= ute=20 query1;
 
    Both the queries do not= give me=20 the desired results. Could anyone suggest a way to get me the=20 values.
 
2. What command or table gives the details= of the=20 constraints created other than check constraints.
 
3. I'am using version 7.1.3. of PostgreSQL= . Any=20 idea whether any of the upcoming versions supports the use of=20 Execute for dynamic queries of Create table as... Select.
 
Any help would be appreciated.
 
Thanx in advance
 
Unni
 
 
 
------=_NextPart_000_0085_01C19C6D.EC299140-- From reedstrm@rice.edu Sun Jan 13 16:19:27 2002 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 From tgl@sss.pgh.pa.us Sun Jan 13 16:59:58 2002 Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0DGxwB21805 for ; Sun, 13 Jan 2002 11:59:58 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g0DGxXg26112; Sun, 13 Jan 2002 11:59:33 -0500 (EST) To: "Unnikrishnan Menon" cc: pgsql-sql@postgresql.org Subject: Re: Problems with PL/PGSQL - functions In-reply-to: <008801c19c3f$d588ce70$e90aa8c0@UNNIKRISHNAN> References: <008801c19c3f$d588ce70$e90aa8c0@UNNIKRISHNAN> Comments: In-reply-to "Unnikrishnan Menon" message dated "Sun, 13 Jan 2002 20:07:27 +0530" Date: Sun, 13 Jan 2002 11:59:33 -0500 Message-ID: <26109.1010941173@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200201/109 X-Sequence-Number: 6198 "Unnikrishnan Menon" 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 From unnikrishnan.menon@chennai.transys.net Mon Jan 14 05:16:20 2002 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