Postgres query to find if foreign-key columns match the data type of the referenced table


create or replace function find_array_element( el anyelement, arr anyarray ) returns integer as $$
declare
i int;
begin
for i in 1..array_upper( arr, 1 ) loop
if( el = arr[i] ) then
return i;
end if;
end loop;

return 0;
end;
$$ language plpgsql;

select
(select relname
from pg_class
where oid = conrelid) as table,
(select relname
from pg_class
where oid = confrelid ) as parent,
conname as foriegn_key,
array(select attname
from pg_attribute
where attrelid = C.conrelid
and attnum = ANY( C.conkey )
order by find_array_element( attnum, C.conkey ) ) as table_columns,
array(select (select typname
from pg_type
where oid = A.atttypid)
from pg_attribute as A
where attrelid = C.conrelid
and attnum = ANY( C.conkey )
order by find_array_element( attnum, C.conkey ) ) as table_datatypes,
array(select attname
from pg_attribute
where attrelid = C.confrelid
and attnum = ANY( C.confkey )
order by find_array_element( attnum, C.confkey ) ) as parent_columns,
array(select (select typname
from pg_type
where oid = A.atttypid)
from pg_attribute as A
where attrelid = C.confrelid
and attnum = ANY( C.confkey )
order by find_array_element( attnum, C.confkey ) ) as parent_datatypes
from pg_constraint as C
where contype = 'f'
and array(select (atttypid, attlen, atttypmod)::text
from pg_attribute
where attrelid = C.conrelid
and attnum = any ( C.conkey )
order by find_array_element( attnum, C.conkey ) )
<> array(select (atttypid, attlen, atttypmod)::text
from pg_attribute
where attrelid = C.confrelid
and attnum = any ( C.confkey )
order by find_array_element( attnum, C.confkey ) );

1 comment:

  1. Just wanted to note that the older version of this query, which used 'ORDER BY attnum' for every ORDER BY clause, wouldn't have worked for the following test case:


    create table t1( a smallint, b bigint, primary key (a,b) );
    create table t2( a smallint, b bigint, foreign key (b,a) references t1(a,b) );

    ReplyDelete