Re: [HACKERS] is it possible to use LIMIT and INTERSECT ?

Tom,

patch was applied smoothly to 6.5.2
What's the syntax ?

select a.msg_id, c.status_set_date, c.title
from Message_Keyword_map a, messages c, keywords d
where c.status_id =1 and d.name ~* 'moon' and a.key_id=d.key_id
and c.msg_id=a.msg_id
intersect
select a.msg_id, a.status_set_date, a.title from messages a
where a.status_id = 1 and a.title ~* 'moon' limit 5;

produces (10 rows)

select a.msg_id, c.status_set_date, c.title
from Message_Keyword_map a, messages c, keywords d
where c.status_id =1 and d.name ~* 'moon' and a.key_id=d.key_id
and c.msg_id=a.msg_id limit 5
intersect
select a.msg_id, a.status_set_date, a.title from messages a
where a.status_id = 1 and a.title ~* 'moon' limit 5;

ERROR: parser: parse error at or near "intersect"

Oleg

On Sun, 17 Oct 1999, Tom Lane wrote:

> Date: Sun, 17 Oct 1999 19:31:09 -0400
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> To: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
> Cc: pgsql-hackers(at)postgreSQL(dot)org
> Subject: Re: [HACKERS] is it possible to use LIMIT and INTERSECT ?
>
> Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> writes:
> > select ......
> > intersect
> > select ......
> > Current implementation of LIMIT doesn't support this.
> > Are there any solutions ?
>
> The problem seems to be right about where I suspected it was...
>
> Try the attached (line numbers are for current, probably are way off
> for 6.5.*, but the code in that routine hasn't changed much).
>
> regards, tom lane
>
>
>
> *** src/backend/rewrite/rewriteHandler.c.orig Thu Oct 7 00:23:15 1999
> --- src/backend/rewrite/rewriteHandler.c Sun Oct 17 19:18:01 1999
> ***************
> *** 1806,1811 ****
> --- 1806,1813 ----
> bool isBinary,
> isPortal,
> isTemp;
> + Node *limitOffset,
> + *limitCount;
> CmdType commandType = CMD_SELECT;
> List *rtable_insert = NIL;
>
> ***************
> *** 1856,1861 ****
> --- 1858,1865 ----
> isBinary = parsetree->isBinary;
> isPortal = parsetree->isPortal;
> isTemp = parsetree->isTemp;
> + limitOffset = parsetree->limitOffset;
> + limitCount = parsetree->limitCount;
>
> /*
> * The operator tree attached to parsetree->intersectClause is still
> ***************
> *** 2057,2062 ****
> --- 2061,2068 ----
> result->isPortal = isPortal;
> result->isBinary = isBinary;
> result->isTemp = isTemp;
> + result->limitOffset = limitOffset;
> + result->limitCount = limitCount;
>
> /*
> * The relation to insert into is attached to the range table of the
>
> ************
>

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83