Kris Jurka wrote:
> The problem arises when trying to join arrays as if they were tables.
>
How 'bout something like this:
create or replace function array_map_attr(smallint[], oid)
returns text[] as '
declare
v_arr alias for $1;
v_toid alias for $2;
v_lb int;
v_ub int;
v_attname text;
v_result text[] := ''{}'';
begin
v_lb := array_lower(v_arr, 1);
v_ub := array_upper(v_arr, 1);
if v_lb is not null and v_ub is not null then
for i in v_lb..v_ub loop
select into v_attname attname::text
from pg_attribute
where attrelid = v_toid and attnum = v_arr[i];
v_result := v_result || v_attname;
end loop;
return v_result;
end if;
return NULL;
end;
' language 'plpgsql' strict;
SELECT
pkn.nspname AS PKTABLE_SCHEM,
pkt.relname AS PKTABLE_NAME,
array_map_attr(c.confkey, pkt.oid) AS PKCOLUMN_NAME,
fkn.nspname AS FKTABLE_SCHEM,
fkt.relname AS FKTABLE_NAME,
array_map_attr(c.conkey, fkt.oid) AS FKCOLUMN_NAME,
c.conname AS FK_NAME,
pkc.conname AS PK_NAME
FROM pg_namespace pkn, pg_class pkt,
pg_namespace fkn, pg_class fkt,
pg_constraint c, pg_constraint pkc
WHERE
pkn.oid = pkt.relnamespace
AND fkn.oid = fkt.relnamespace
AND c.conrelid = fkt.oid
AND c.confrelid = pkt.oid
AND c.confrelid = pkc.conrelid
;
-[ RECORD 1 ]-+-----------------
pktable_schem | public
pktable_name | clstr_tst_s
pkcolumn_name | {rf_a}
fktable_schem | public
fktable_name | clstr_tst
fkcolumn_name | {b}
fk_name | clstr_tst_con
pk_name | clstr_tst_s_pkey
-[ RECORD 2 ]-+-----------------
pktable_schem | public
pktable_name | rfi1
pkcolumn_name | {f1,f2}
fktable_schem | public
fktable_name | rfi2
fkcolumn_name | {f1,f2}
fk_name | $1
pk_name | rfi1_pkey
It wouldn't be hard to turn array_map_attr() (or whatever name) into a C
function.
Joe
