Re: [HACKERS] is it possible to use LIMIT and INTERSECT ? - Mailing list pgsql-hackers

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@sss.pgh.pa.us>
> To: Oleg Bartunov <oleg@sai.msu.su>
> Cc: pgsql-hackers@postgreSQL.org
> Subject: Re: [HACKERS] is it possible to use LIMIT and INTERSECT ? 
> 
> Oleg Bartunov <oleg@sai.msu.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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83