Message-ID: <00d601c39ed3$7da18010$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "Christoph Haller" <ch@rodos.fzk.de>
Cc: <pgsql-sql@postgresql.org>
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

Hi Christoph Haller,

Thanks you very much. It worked.

----- Original Message ----- 
From: "Christoph Haller" <ch@rodos.fzk.de>
To: ""Kumar"" <sgnerd@yahoo.com.sg>
Cc: <pgsql-sql@postgresql.org>
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