Re: How to compare the results of two queries?

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Juan Daniel Santana Rodés
> Sent: Tuesday, September 17, 2013 11:00 AM
> To: pgsql-general(at)postgresql(dot)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