Re: How to compare the results of two queries?

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Igor Neyman
> Sent: Tuesday, September 17, 2013 12:02 PM
> To: Juan Daniel Santana Rodés; pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] 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
>

Well, if you really want to use "limit" clause in your queries, the following should work (even with the "limit"):

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';

Regards,
Igor Neyman