Re: How to compare the results of two queries? - Mailing list pgsql-general

From Igor Neyman
Subject Re: How to compare the results of two queries?
Date
Msg-id A76B25F2823E954C9E45E32FA49D70EC4281FB2D@mail.corp.perceptron.com
Whole thread Raw
In response to Re: How to compare the results of two queries?  (Igor Neyman <ineyman@perceptron.com>)
List pgsql-general

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Igor Neyman
> Sent: Tuesday, September 17, 2013 12:02 PM
> To: Juan Daniel Santana Rodés; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] How to compare the results of two queries?
> 
> 
> 
> > -----Original Message-----
> > From: Juan Daniel Santana Rodés [mailto:jdsantana@estudiantes.uci.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

pgsql-general by date:

Previous
From: Adam C Falkenberg
Date:
Subject: Re: Using ODBC and VBA to pull data from a large object
Next
From: Juan Daniel Santana Rodés
Date:
Subject: Re: How to compare the results of two queries?