Re: How to compare the results of two queries?

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