Re: How to compare the results of two queries? - Mailing list 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
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>