While executing this I got the following error

WARNING: plpgsql: ERROR during compile of=20 list_history near line 5

ERROR: syntax error at "("

While I execute the following code it is working fine a= nd=20 fetches values.

(SELECT * FROM history = WHERE=20 obs_type =3D \'AA\' )
UNION
(SELECT *=20 FROM history WHERE obs_type =3D=20 \'TA\');

Where I am wrong. Please shed some light,

Regards

Kumar

------=_NextPart_000_006F_01C39EF1.6F161150-- From ch@rodos.fzk.de Thu Oct 30 09:26:58 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 15F3FD1B4E5 for ; Thu, 30 Oct 2003 09:26:58 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 16521-08 for ; Thu, 30 Oct 2003 05:26:28 -0400 (AST) Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52]) by svr1.postgresql.org (Postfix) with SMTP id 930B2D1B500 for ; Thu, 30 Oct 2003 05:26:24 -0400 (AST) Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ; Thu Oct 30 10:26:19 2003 +0100 Received: śby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id KAA22447; Thu, 30 Oct 2003 10:26:18 +0100 (MET) From: Christoph Haller Message-Id: <200310300926.KAA04661@rodos> Subject: Re: Using UNION inside a cursor To: sgnerd@yahoo.com.sg ("Kumar") Date: Thu, 30 Oct 2003 10:26:17 MET Cc: pgsql-sql@postgresql.org In-Reply-To: <007c01c39ec4$5af992f0$7502a8c0@hdsc.com>; from "Kumar" at Oct 30, 103 10:11 am X-Mailer: Elm [revision: 212.4] X-Virus-Scanned: by amavisd-new at postgresql.org X-Amavis-Alert: BAD HEADER Non-encoded 8-bit data (char 9C hex) in message header 'Received' Received: \234by rodos.fzk.de... ^ X-Archive-Number: 200310/394 X-Sequence-Number: 15794 > > Dear Friends, > > I am working on Postgresql 7.3.4 on RH Linux Server 7.3.=20 > > I have problem in executing the following procedure > > CREATE OR REPLACE FUNCTION list_history() > RETURNS refcursor AS > 'DECLARE > ref REFCURSOR; > BEGIN > OPEN ref FOR > (SELECT * FROM history WHERE obs_type =3D \'AA\' ) > UNION=20 > (SELECT * FROM history WHERE obs_type =3D \'TA\'); > > RETURN ref; > END;' > LANGUAGE 'plpgsql' VOLATILE; > > While executing this I got the following error > > WARNING: plpgsql: ERROR during compile of list_history near line 5 > ERROR: syntax error at "(" > > While I execute the following code it is working fine and fetches values. > (SELECT * FROM history WHERE obs_type =3D \'AA\' ) > UNION=20 > (SELECT * FROM history WHERE obs_type =3D \'TA\'); > > Where I am wrong. Please shed some light, > > Regards > Kumar > My suspicion is the plpgsql parser doesn't accept the opening parenthesis. What happens on OPEN ref FOR SELECT * FROM ( (SELECT * FROM history WHERE obs_type =3D \'AA\' ) UNION=20 (SELECT * FROM history WHERE obs_type =3D \'TA\') ) ; Regards, Christoph From sgnerd@yahoo.com.sg Thu Oct 30 10:52:23 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id D4B92D1B51A for ; Thu, 30 Oct 2003 10:52:23 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 23495-06 for ; Thu, 30 Oct 2003 06:51:55 -0400 (AST) Received: from smtp012.mail.yahoo.com (smtp012.mail.yahoo.com [216.136.173.32]) by svr1.postgresql.org (Postfix) with SMTP id 0ED55D1B510 for ; Thu, 30 Oct 2003 06:51:52 -0400 (AST) Received: from ptil-138-190-chn.primus-india.net (HELO IVESIA67) (sgnerd@203.196.190.138 with login) by smtp.mail.vip.sc5.yahoo.com with SMTP; 30 Oct 2003 10:51:53 -0000 Message-ID: <00d601c39ed3$7da18010$7502a8c0@hdsc.com> Reply-To: "Kumar" From: "Kumar" To: "Christoph Haller" Cc: References: <200310300926.KAA04661@rodos> Subject: Re: Using UNION inside a cursor Date: Thu, 30 Oct 2003 16:19:21 +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 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.6 tagged_above=0.0 required=5.0 tests=BAYES_30, QUOTED_EMAIL_TEXT, REFERENCES, UPPERCASE_25_50, WEIRD_QUOTING X-Spam-Level: * X-Archive-Number: 200310/395 X-Sequence-Number: 15795 Hi Christoph Haller, Thanks you very much. It worked. ----- Original Message ----- From: "Christoph Haller" To: ""Kumar"" Cc: Sent: Thursday, October 30, 2003 3:56 PM Subject: Re: [SQL] Using UNION inside a cursor > > > > Dear Friends, > > > > I am working on Postgresql 7.3.4 on RH Linux Server 7.3.=20 > > > > I have problem in executing the following procedure > > > > CREATE OR REPLACE FUNCTION list_history() > > RETURNS refcursor AS > > 'DECLARE > > ref REFCURSOR; > > BEGIN > > OPEN ref FOR > > (SELECT * FROM history WHERE obs_type =3D \'AA\' ) > > UNION=20 > > (SELECT * FROM history WHERE obs_type =3D \'TA\'); > > > > RETURN ref; > > END;' > > LANGUAGE 'plpgsql' VOLATILE; > > > > While executing this I got the following error > > > > WARNING: plpgsql: ERROR during compile of list_history near line 5 > > ERROR: syntax error at "(" > > > > While I execute the following code it is working fine and fetches values. > > (SELECT * FROM history WHERE obs_type =3D \'AA\' ) > > UNION=20 > > (SELECT * FROM history WHERE obs_type =3D \'TA\'); > > > > Where I am wrong. Please shed some light, > > > > Regards > > Kumar > > > My suspicion is the plpgsql parser doesn't accept the opening > parenthesis. What happens on > > OPEN ref FOR SELECT * FROM ( > (SELECT * FROM history WHERE obs_type =3D \'AA\' ) > UNION=20 > (SELECT * FROM history WHERE obs_type =3D \'TA\') > ) ; > > Regards, Christoph From tgl@sss.pgh.pa.us Thu Oct 30 15:21:27 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 16627D1B544 for ; Thu, 30 Oct 2003 15:21:27 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 69634-10 for ; Thu, 30 Oct 2003 11:20:59 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 735A2D1B51A for ; Thu, 30 Oct 2003 11:20:39 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id h9UFKRit028147; Thu, 30 Oct 2003 10:20:28 -0500 (EST) To: Christoph Haller Cc: sgnerd@yahoo.com.sg ("Kumar"), pgsql-sql@postgresql.org Subject: Re: Using UNION inside a cursor In-reply-to: <200310300926.KAA04661@rodos> References: <200310300926.KAA04661@rodos> Comments: In-reply-to Christoph Haller message dated "Thu, 30 Oct 2003 10:26:17 +0700" Date: Thu, 30 Oct 2003 10:20:27 -0500 Message-ID: <28146.1067527227@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/402 X-Sequence-Number: 15802 Christoph Haller writes: >> OPEN ref FOR >> (SELECT * FROM history WHERE obs_type =3D \'AA\' ) >> UNION >> (SELECT * FROM history WHERE obs_type =3D \'TA\'); > My suspicion is the plpgsql parser doesn't accept the opening > parenthesis. Yeah. Looking at the plpgsql grammar, it expects the token after FOR to be either SELECT or EXECUTE. This example demonstrates that that code is wrong, so I'll fix it for 7.4. But in the meantime, why don't you just leave out the parentheses? regards, tom lane From sgnerd@yahoo.com.sg Fri Oct 31 03:57:04 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7B5ABD1B511 for ; Fri, 31 Oct 2003 03:57:04 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 93963-10 for ; Thu, 30 Oct 2003 23:56:37 -0400 (AST) Received: from smtp015.mail.yahoo.com (smtp015.mail.yahoo.com [216.136.173.59]) by svr1.postgresql.org (Postfix) with SMTP id 9A308D1B53A for ; Thu, 30 Oct 2003 23:56:32 -0400 (AST) Received: from ptil-138-190-chn.primus-india.net (HELO IVESIA67) (sgnerd@203.196.190.138 with login) by smtp.mail.vip.sc5.yahoo.com with SMTP; 31 Oct 2003 03:56:36 -0000 Message-ID: <007001c39f62$a44088d0$7502a8c0@hdsc.com> Reply-To: "Kumar" From: "Kumar" To: "Christoph Haller" , "Tom Lane" Cc: References: <200310300926.KAA04661@rodos> <28146.1067527227@sss.pgh.pa.us> Subject: Re: Using UNION inside a cursor Date: Fri, 31 Oct 2003 09:16:51 +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 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.6 tagged_above=0.0 required=5.0 tests=BAYES_30, QUOTED_EMAIL_TEXT, REFERENCES, WEIRD_QUOTING X-Spam-Level: X-Archive-Number: 200310/408 X-Sequence-Number: 15808 Hi Tom, I need the parentheses, because the where condition is different for the 1 and 2 nd query . Moreover my actual use includes a 'Limit' clause also. so the parentheses is needed. I have tried with execute but it showed error. But I have managed to fix the problem like this. OPEN ref FOR SELECT * FROM (SELECT * FROM history WHERE obs_type =3D \'AA\' ) UNION (SELECT * FROM history WHERE obs_type =3D \'TA\') as foo; Regards Kumar ----- Original Message ----- From: "Tom Lane" To: "Christoph Haller" Cc: ""Kumar"" ; Sent: Thursday, October 30, 2003 8:50 PM Subject: Re: [SQL] Using UNION inside a cursor > Christoph Haller writes: > >> OPEN ref FOR > >> (SELECT * FROM history WHERE obs_type =3D \'AA\' ) > >> UNION > >> (SELECT * FROM history WHERE obs_type =3D \'TA\'); > > > My suspicion is the plpgsql parser doesn't accept the opening > > parenthesis. > > Yeah. Looking at the plpgsql grammar, it expects the token after FOR to > be either SELECT or EXECUTE. This example demonstrates that that code > is wrong, so I'll fix it for 7.4. But in the meantime, why don't you > just leave out the parentheses? > > regards, tom lane From paulsnewsgroups@hotmail.com Tue Nov 4 15:06:59 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 5CEB5D1B561 for ; Tue, 4 Nov 2003 15:06:59 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 12563-06 for ; Tue, 4 Nov 2003 11:06:32 -0400 (AST) Received: from main.gmane.org (main.gmane.org [80.91.224.249]) by svr1.postgresql.org (Postfix) with ESMTP id 976BBD1CB0E for ; Tue, 4 Nov 2003 11:06:28 -0400 (AST) Received: from list by main.gmane.org with local (Exim 3.35 #1 (Debian)) id 1AH2lM-0000mC-00 for ; Tue, 04 Nov 2003 16:06:32 +0100 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-sql@postgresql.org Received: from sea.gmane.org ([80.91.224.252]) by main.gmane.org with esmtp (Exim 3.35 #1 (Debian)) id 1AH2lL-0000m4-00 for ; Tue, 04 Nov 2003 16:06:31 +0100 Received: from news by sea.gmane.org with local (Exim 3.35 #1 (Debian)) id 1AH2lL-0005T2-00 for ; Tue, 04 Nov 2003 16:06:31 +0100 From: Paul Ganainm Subject: Re: Using UNION inside a cursor Date: Tue, 4 Nov 2003 15:02:57 -0000 Organization: Néant Lines: 27 Message-ID: References: <007c01c39ec4$5af992f0$7502a8c0@hdsc.com> X-Complaints-To: usenet@sea.gmane.org X-Newsreader: MicroPlanet Gravity v2.50 X-Virus-Scanned: by amavisd-new at postgresql.org X-Amavis-Alert: BAD HEADER Non-encoded 8-bit data (char E9 hex) in message header 'Organization' Organization: N\351ant\n ^ X-Archive-Number: 200311/15 X-Sequence-Number: 15830 sgnerd@yahoo.com.sg says... > (SELECT * FROM history WHERE obs_type = \'AA\' ) > UNION > (SELECT * FROM history WHERE obs_type = \'TA\'); Maybe I'm just confused here, but what's to stop you using SELECT * FROM History WHERE (Obs_Type = \'AA\' AND Obs_Type = \'TA\') ? Or have I missed something really obvious? Paul... -- plinehan__AT__yahoo__DOT__com C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro Please do not top-post.