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

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