Thread: 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 

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

Christoph Haller <ch@rodos.fzk.de> 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

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" <tgl@sss.pgh.pa.us>
To: "Christoph Haller" <ch@rodos.fzk.de>
Cc: ""Kumar"" <sgnerd@yahoo.com.sg>; <pgsql-sql@postgresql.org>
Sent: Thursday, October 30, 2003 8:50 PM
Subject: Re: [SQL] Using UNION inside a cursor


> Christoph Haller <ch@rodos.fzk.de> 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

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.