select diff rows.sql

with tabela as (
    select format('%s.%s', quote_ident(nsp.nspname), quote_ident(t.tablename)) as tablename, c.oid
    from pg_catalog.pg_tables t
    inner join pg_catalog.pg_class c on c.relname = t.tablename
    inner join pg_catalog.pg_namespace nsp on nsp.nspname = t.schemaname and nsp.oid = c.relnamespace
    where t.tablename = :tablename and nsp.nspname = :schemaname
), cases1 as (
    select
        format('when i = %s and a.%s <> b.%s then ''%s''', row_number() over() - 1, quote_ident(att.attname), quote_ident(att.attname), quote_ident(att.attname)) as case1,
        format('when i = %s and a.%s <> b.%s then a.%s::text', row_number() over() - 1, quote_ident(att.attname), quote_ident(att.attname), quote_ident(att.attname)) as case2,
        format('when i = %s and a.%s <> b.%s then b.%s::text', row_number() over() - 1, quote_ident(att.attname), quote_ident(att.attname), quote_ident(att.attname)) as case3,
        t.tablename
    from pg_catalog.pg_attribute att
    inner join tabela t on t.oid = att.attrelid
    where att.attnum > 0
), cases2 as (
    select
        format('case %s end', string_agg(case1, E'\n')) as case1,
        format('case %s end', string_agg(case2, E'\n')) as case2,
        format('case %s end', string_agg(case3, E'\n')) as case3,
        count(*) as n_cols
    from cases1 c
    group by tablename
), index_cols_match as (
    SELECT 
        format('(%s) = (%s)',
            string_agg('a.'||quote_ident(a.attname), ','),
            string_agg(':a_'||quote_ident(a.attname), ',')
            ) as key_cond1,
        format('(%s) = (%s)',
            string_agg('b.'||quote_ident(a.attname), ','),
            string_agg(':b_'||quote_ident(a.attname), ',')
            ) as key_cond2
    FROM   pg_index i
    JOIN   pg_attribute a ON a.attrelid = i.indrelid
                         AND a.attnum = ANY(i.indkey),
    tabela t
    WHERE  i.indrelid = t.oid
    AND    i.indisprimary
    group by t.tablename
)
select 
    format(
'select %s, %s, %s
from %s a, %s b, generate_series(0, %s - 1) t(i)
where %s and %s
and %s is not null', case1, case2, case3, tablename, tablename, n_cols, key_cond1, key_cond2, case1)
from cases2, tabela, index_cols_match;