Re: How to compare the results of two queries?

Juan Daniel Santana Rodés <jdsantana(at)estudiantes(dot)uci(dot)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