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