Re: How to compare the results of two queries?

On Tue, 17 Sep 2013 10:59:43 -0400
Juan Daniel Santana Rodés <jdsantana(at)estudiantes(dot)uci(dot)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(at)yahoo(dot)es>