How to compare the results of two queries?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 table2 tables 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 on a hstore data column.

Thanks in advance.
Best regards from Cuba.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general