Thread: How to compare the results of two queries?

How to compare the results of two queries?

From

Juan Daniel Santana Rodés

Date:

17 September 2013, 15:00:19

I am developing a task in which I need to know how to compare the
results of two queries ...
I thought about creating a procedure which both queries received by
parameters respectively. Then somehow able to run queries and return if
both have the same result. As a feature of the problem, both queries are
selection.
Here I leave a piece of code I want to do.

create or replace function compare(sql1 character varying, sql2
character varying) returns boolean as
$body$
Declare
Begin
--here in some way to run both queries and then compare
End;
$body$
language 'plpgsql';

I've been studying and I found that there EXECUTE but to use it, first
you should have used PREPARE, and in this case the values of the
parameters are already made inquiries.
For example the execution of the function would be something like ...

select compare('select * from table1', 'select * from table2');

For this case the result is false, then the queries are executed on
different tables.
Thanks in advance.
Best regards from Cuba.
__________________________________________________
"Todos el 12 de Septiembre con una Cinta Amarilla"
FIN A LA INJUSTICIA, LIBERENLOS YA!!
http://www.antiterroristas.cu
http://justiciaparaloscinco.wordpress.com

Re: How to compare the results of two queries?

From

Raymond O'Donnell

Date:

17 September 2013, 15:17:43

On 17/09/2013 15:59, Juan Daniel Santana Rodés wrote:
> I am developing a task in which I need to know how to compare the
> results of two queries ...
> I thought about creating a procedure which both queries received by
> parameters respectively. Then somehow able to run queries and return if
> both have the same result. As a feature of the problem, both queries are
> selection.
> Here I leave a piece of code I want to do.
>
> create or replace function compare(sql1 character varying, sql2
> character varying) returns boolean as
> $body$
> Declare
> Begin
> --here in some way to run both queries and then compare
> End;
> $body$
> language 'plpgsql';
>
> I've been studying and I found that there EXECUTE but to use it, first
> you should have used PREPARE, and in this case the values of the

EXECUTE in pl/pgsql is different to EXECUTE in ordinary SQL; it's used
for executing queries constructed on-the-fly as strings. You don't need
to do a PREPARE before EXECUTE in a pl/pgsql function.

Here's the relevant place in the docs for this form of EXECUTE:

http://www.postgresql.org/docs/9.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN


Ray.



--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Re: How to compare the results of two queries?

From

Igor Neyman

Date:

17 September 2013, 15:27:20

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Juan Daniel Santana Rodés
> Sent: Tuesday, September 17, 2013 11:00 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] How to compare the results of two queries?
> 
> I am developing a task in which I need to know how to compare the results of
> two queries ...
> I thought about creating a procedure which both queries received by
> parameters respectively. Then somehow able to run queries and return if
> both have the same result. As a feature of the problem, both queries are
> selection.
> Here I leave a piece of code I want to do.
> 
> create or replace function compare(sql1 character varying, sql2 character
> varying) returns boolean as $body$ Declare Begin --here in some way to run
> both queries and then compare End; $body$ language 'plpgsql';
> 
> I've been studying and I found that there EXECUTE but to use it, first you
> should have used PREPARE, and in this case the values of the parameters are
> already made inquiries.
> For example the execution of the function would be something like ...
> 
> select compare('select * from table1', 'select * from table2');
> 
> For this case the result is false, then the queries are executed on different
> tables.
> Thanks in advance.
> Best regards from Cuba.

EXECUTE in PgPlsql does not require PREPARE.
So, something like this:

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


should work.  Be aware, I didn't test it.

Regards,
Igor Neyman

Re: How to compare the results of two queries?

From

Luca Ferrari

Date:

17 September 2013, 15:31:11

On Tue, Sep 17, 2013 at 4:59 PM, Juan Daniel Santana Rodés
<jdsantana@estudiantes.uci.cu> wrote:

> For example the execution of the function would be something like ...
>
> select compare('select * from table1', 'select * from table2');
>
> For this case the result is false, then the queries are executed on
> different tables.

I suppose you are taking for sure that both queries references tables
with the same structure, in such case why not computing an hash of
each row to check against the other result set?
About how many rows are we talking? Because it sounds to me like a
good job for a diff-like external tool, is this a possible solution?
Have you considered that the tables could have a different structure
or even just a different layout, in such case a "select *" will return
different results while the data is actually the same?
What is the aim of this?

Luca

Re: How to compare the results of two queries?

From

Igor Neyman

Date:

17 September 2013, 16:02:23


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

Re: How to compare the results of two queries?

From

Igor Neyman

Date:

17 September 2013, 16:44:59


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

Re: How to compare the results of two queries?

From

Juan Daniel Santana Rodés

Date:

17 September 2013, 16:51:21

El 17/09/13 12:02, Igor Neyman escribió:
>
>> -----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
>
I want to know if there are other way to compare the result of two queries.
Because the arguments will represent a query to execute and it can use
everything sentence of SQL.
If not there are other way, I wish know who are the limitations of EXCEPT.
Greatens!!
__________________________________________________
"Todos el 12 de Septiembre con una Cinta Amarilla"
FIN A LA INJUSTICIA, LIBERENLOS YA!!
http://www.antiterroristas.cu
                                    
http://justiciaparaloscinco.wordpress.com

Re: How to compare the results of two queries?

From

Eduardo Morras

Date:

17 September 2013, 16:54:48

On Tue, 17 Sep 2013 10:59:43 -0400
Juan Daniel Santana Rodés <jdsantana@estudiantes.uci.cu> wrote:

> I've been studying and I found that there EXECUTE but to use it, first
> you should have used PREPARE, and in this case the values of the
> parameters are already made inquiries.
> For example the execution of the function would be something like ...
>
> select compare('select * from table1', 'select * from table2');
>
> For this case the result is false, then the queries are executed on
> different tables.

If you create a table with:

CREATE TABLE comp
(
  result1 : hstore,
  result2 : hstore
);

insert in it the rows from selects:

INSERT INTO comp(result1, result2)
  (SELECT * FROM table1, SELECT * FROM table2);

Substitute (SELECT * FROM table, SELECT * FROM table2) with your queries, store the result of the queries on table1 and
table2tables or use a WITH in the INSERT. 

you can get the differences between both queries using '-' hstore operator:

SELECT (result1 - result2) as LEFT, (result2 - result1) as RIGHT FROM comp;

Or simulating an equal instruction:

SELECT (COUNT(result1 - result2)+COUNT(result2 - result1)=0) FROM comp; -- Not sure about this one because uses COUNT
ona hstore data column. 

> Thanks in advance.
> Best regards from Cuba.


---   ---
Eduardo Morras <emorrasg@yahoo.es>

Re: How to compare the results of two queries?

From

Igor Neyman

Date:

17 September 2013, 16:56:23


> -----Original Message-----
> From: Juan Daniel Santana Rodés [mailto:jdsantana@estudiantes.uci.cu]
> Sent: Tuesday, September 17, 2013 12:51 PM
> To: Igor Neyman
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] How to compare the results of two queries?
> 
> >
> I want to know if there are other way to compare the result of two queries.
> Because the arguments will represent a query to execute and it can use
> everything sentence of SQL.
> If not there are other way, I wish know who are the limitations of EXCEPT.
> Greatens!!
> __________________________________________________


In the modified function I put both queries in parenthesis, so this should allow pretty much anything in the query.

Igor Neyman

Re: How to compare the results of two queries?

From

Juan Daniel Santana Rodés

Date:

17 September 2013, 17:38:43

El 17/09/13 12:56, Igor Neyman escribió:
>
>> -----Original Message-----
>> From: Juan Daniel Santana Rodés [mailto:jdsantana@estudiantes.uci.cu]
>> Sent: Tuesday, September 17, 2013 12:51 PM
>> To: Igor Neyman
>> Cc: pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] How to compare the results of two queries?
>>
>> I want to know if there are other way to compare the result of two queries.
>> Because the arguments will represent a query to execute and it can use
>> everything sentence of SQL.
>> If not there are other way, I wish know who are the limitations of EXCEPT.
>> Greatens!!
>> __________________________________________________
>
> In the modified function I put both queries in parenthesis, so this should allow pretty much anything in the query.
>
> Igor Neyman
Thanks.
I tested your code and worked fine.
Now I only should catch the exception when the results of the querires
has diferents munbers of columns.
God bless you.
__________________________________________________
"Todos el 12 de Septiembre con una Cinta Amarilla"
FIN A LA INJUSTICIA, LIBERENLOS YA!!
http://www.antiterroristas.cu
http://justiciaparaloscinco.wordpress.com

Re: How to compare the results of two queries?

From

Kevin Grittner

Date:

17 September 2013, 18:28:53

Juan Daniel Santana Rodés <jdsantana@estudiantes.uci.cu> wrote:

> I am developing a task in which I need to know how to compare the
> results of two queries ...
> I thought about creating a procedure which both queries received by
> parameters respectively. Then somehow able to run queries and return if
> both have the same result. As a feature of the problem, both queries are
> selection.

Maybe something roughly like this?:

create or replace function rscmp(qry1 text, qry2 text)
  returns boolean
  language plpgsql
as $$
declare
  c int;
begin
  execute 'select count(*) from ('
       || qry1
       || ') rs1 full join ('
       || qry2
       || ') rs2 on rs1 = rs2 where rs1 is not distinct from null or rs2 is not distinct from null'
    into c;
  return (c = 0);
exception
  when sqlstate '42804' then return false;
end;
$$;

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: How to compare the results of two queries?

From

Igor Neyman

Date:

17 September 2013, 18:31:20


> -----Original Message-----
> From: Juan Daniel Santana Rodés [mailto:jdsantana@estudiantes.uci.cu]
> Sent: Tuesday, September 17, 2013 1:38 PM
> To: Igor Neyman
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] How to compare the results of two queries?
> 
> El 17/09/13 12:56, Igor Neyman escribió:
> >
> >> -----Original Message-----
> >> From: Juan Daniel Santana Rodés [mailto:jdsantana@estudiantes.uci.cu]
> >> Sent: Tuesday, September 17, 2013 12:51 PM
> >> To: Igor Neyman
> >> Cc: pgsql-general@postgresql.org
> >> Subject: Re: [GENERAL] How to compare the results of two queries?
> >>
> >> I want to know if there are other way to compare the result of two
> queries.
> >> Because the arguments will represent a query to execute and it can
> >> use everything sentence of SQL.
> >> If not there are other way, I wish know who are the limitations of EXCEPT.
> >> Greatens!!
> >> __________________________________________________
> >
> > In the modified function I put both queries in parenthesis, so this should
> allow pretty much anything in the query.
> >
> > Igor Neyman
> Thanks.
> I tested your code and worked fine.
> Now I only should catch the exception when the results of the querires has
> diferents munbers of columns.
> God bless you.
> __________________________________________________
> "Todos el 12 de Septiembre con una Cinta Amarilla"
> FIN A LA INJUSTICIA, LIBERENLOS YA!!
> http://www.antiterroristas.cu
> http://justiciaparaloscinco.wordpress.com

And of course, not just number of columns in the result sets, but their types should match as well.

Igor Neyman