Thread: using INTERSECT and UNION in IN clause

Hi,

postgresql 7.0.2. Why by executing the following query

select * from magazine
where id in (     select mag_id from dict where word = 'akademie' intersect     select mag_id from dict where word =
'der'intersect     select mag_id from dict where word = 'klasse' )
 

I receive the following error:

ERROR: parse error at or near 'intersect'

while the query
     select mag_id from dict where word = 'akademie' intersect     select mag_id from dict where word = 'der' intersect
   select mag_id from dict where word = 'klasse' )
 

is executed successfully.

Is it possible to use INTERSECT and UNION keywords in subqueries?


Regards,
Alex

Hi everyone,

After fiddling for about a day to work out why my pl/pgsql stored 
procedures weren't working I finally
discovered why. It seems that pl/pgsql has a problem parsing Window style 
new line characters. I
was writing my stored procedures using a Windows app, and then running them 
on my Linux
Postgres database. It keeped on giving me...
an error found one line 1 near " "
which wasn't very helpful.

Anyway just make sure you write your stored procs in your Unix environment 
or save them as Unix
format. I hope this tip saves somebody some time. :)

Cheers,
Keith.

Alex Guryanow <gav@nlr.ru> writes:
> Is it possible to use INTERSECT and UNION keywords in subqueries?

No, not at the moment.  This is one of many things we hope to fix when
we redesign querytrees (currently planned for 7.2 cycle).
        regards, tom lane

Keith Wong <keith@e-magine.com.au> writes:
> It seems that pl/pgsql has a problem parsing Window style 
> new line characters.

Ah-hah, good catch!  I have fixed this bug for 7.1.  If you want to
patch your local copy, the critical changes are:


*** src/pl/plpgsql/src/scan.l.orig    Thu Jun 22 19:08:34 2000
--- src/pl/plpgsql/src/scan.l    Tue Aug 22 10:59:28 2000
***************
*** 143,155 ****      * Ignore whitespaces but remember this happened      * ----------      */
! [ \t\n]+        { plpgsql_SpaceScanned = 1;        }      /* ----------      * Eat up comments      * ----------
*/
! --[^\n]*        ; \/\*            { start_lineno = yylineno;               BEGIN IN_COMMENT;             }
--- 146,158 ----      * Ignore whitespaces but remember this happened      * ----------      */
! [ \t\r\n]+        { plpgsql_SpaceScanned = 1;        }      /* ----------      * Eat up comments      * ----------
 */
 
! --[^\r\n]*        ; \/\*            { start_lineno = yylineno;               BEGIN IN_COMMENT;             }

        regards, tom lane