Re: array support patch phase 1 patch

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