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 
> To: Oleg Bartunov 
> Cc: pgsql-hackers@postgreSQL.org
> Subject: Re: [HACKERS] is it possible to use LIMIT and INTERSECT ? 
> 
> Oleg Bartunov  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