pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing UNION inside a cursor
6+ messages / 4 participants
[nested] [flat]
* Using UNION inside a cursor
@ 2003-10-30 08:53 Kumar <sgnerd@yahoo.com.sg>
2003-10-30 10:26 ` Re: Using UNION inside a cursor Christoph Haller <ch@rodos.fzk.de>
2003-11-04 15:02 ` Re: Using UNION inside a cursor Paul Ganainm <paulsnewsgroups@hotmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Kumar @ 2003-10-30 08:53 UTC (permalink / raw)
To: pgsql-sql
Dear Friends,
I am working on Postgresql 7.3.4 on RH Linux Server 7.3.
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 = \'AA\' )
UNION
(SELECT * FROM history WHERE obs_type = \'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 = \'AA\' )
UNION
(SELECT * FROM history WHERE obs_type = \'TA\');
Where I am wrong. Please shed some light,
Regards
Kumar
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Using UNION inside a cursor
2003-10-30 08:53 Using UNION inside a cursor Kumar <sgnerd@yahoo.com.sg>
@ 2003-10-30 10:26 ` Christoph Haller <ch@rodos.fzk.de>
2003-10-30 10:49 ` Re: Using UNION inside a cursor Kumar <sgnerd@yahoo.com.sg>
2003-10-30 15:20 ` Re: Using UNION inside a cursor Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 6+ messages in thread
From: Christoph Haller @ 2003-10-30 10:26 UTC (permalink / raw)
To: sgnerd@yahoo.com.sg; +Cc: pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Using UNION inside a cursor
2003-10-30 08:53 Using UNION inside a cursor Kumar <sgnerd@yahoo.com.sg>
2003-10-30 10:26 ` Re: Using UNION inside a cursor Christoph Haller <ch@rodos.fzk.de>
@ 2003-10-30 10:49 ` Kumar <sgnerd@yahoo.com.sg>
1 sibling, 0 replies; 6+ messages in thread
From: Kumar @ 2003-10-30 10:49 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Using UNION inside a cursor
2003-10-30 08:53 Using UNION inside a cursor Kumar <sgnerd@yahoo.com.sg>
2003-10-30 10:26 ` Re: Using UNION inside a cursor Christoph Haller <ch@rodos.fzk.de>
@ 2003-10-30 15:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-31 03:46 ` Re: Using UNION inside a cursor Kumar <sgnerd@yahoo.com.sg>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2003-10-30 15:20 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: sgnerd@yahoo.com.sg; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Using UNION inside a cursor
2003-10-30 08:53 Using UNION inside a cursor Kumar <sgnerd@yahoo.com.sg>
2003-10-30 10:26 ` Re: Using UNION inside a cursor Christoph Haller <ch@rodos.fzk.de>
2003-10-30 15:20 ` Re: Using UNION inside a cursor Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-10-31 03:46 ` Kumar <sgnerd@yahoo.com.sg>
0 siblings, 0 replies; 6+ messages in thread
From: Kumar @ 2003-10-31 03:46 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Using UNION inside a cursor
2003-10-30 08:53 Using UNION inside a cursor Kumar <sgnerd@yahoo.com.sg>
@ 2003-11-04 15:02 ` Paul Ganainm <paulsnewsgroups@hotmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Paul Ganainm @ 2003-11-04 15:02 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-11-04 15:02 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-30 08:53 Using UNION inside a cursor Kumar <sgnerd@yahoo.com.sg>
2003-10-30 10:26 ` Christoph Haller <ch@rodos.fzk.de>
2003-10-30 10:49 ` Kumar <sgnerd@yahoo.com.sg>
2003-10-30 15:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-31 03:46 ` Kumar <sgnerd@yahoo.com.sg>
2003-11-04 15:02 ` Paul Ganainm <paulsnewsgroups@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox