Re: How to compare the results of two queries?

> -----Original Message-----
> From: Juan Daniel Santana Rodés [mailto:jdsantana(at)estudiantes(dot)uci(dot)cu]
> Sent: Tuesday, September 17, 2013 11:54 AM
> To: Igor Neyman
> Subject: Re: [GENERAL] How to compare the results of two queries?
>
> El 17/09/13 11:27, Igor Neyman escribió:
> > create or replace function compare(sql1 character varying, sql2
> > character varying) returns boolean as $body$ Declare lCount int := 0;
> > Begin
> >
> > EXECUTE 'SELECT COUNT(Res.*) FROM ( (' || sql1 || ' EXCEPT ' || sql2
> > || ') UNION (' || sql2 || ' EXCEPT ' || sql1 || ') ) Res' INTO lCount; IF (lCount
> = 0)
> > RETURN TRUE;
> > ELSE
> > RETURN FALSE;
> > END IF;
> >
> > End;
> > $body$ language 'plpgsql';
>
> Hi, thank for your help...
> I'm trying to execute your code but, when I run the the sentence, it throw a
> exception.
> For example, I run this line...
>
> select compare('select * from point limit 2', 'select * from point');
>
> And, postgres throw the follow exceptio...
>
> ERROR: syntax error at or near "EXCEPT"
> LINE 1: ...COUNT(Res.*) FROM ( (select * from point limit 2 EXCEPT sel...
> ^
> QUERY: SELECT COUNT(Res.*) FROM ( (select * from point limit 2 EXCEPT
> select * from point) UNION (select * from point EXCEPT select * from point
> limit 2) ) Res
> CONTEXT: PL/pgSQL function "compare" line 5 at EXECUTE statement
>
> ********** Error **********
>
> ERROR: syntax error at or near "EXCEPT"
> Estado SQL:42601
> Contexto:PL/pgSQL function "compare" line 5 at EXECUTE statement
>

"limit 2" does not work with "EXCEPT".

In the future reply to the list ("Reply All") in order to keep the list in the conversation.

Regards,
Igor Neyman