Re: Referential cascade technique - Mailing list pgsql-general

Mike

I use the following PLPERL/select "code" to view all FK's in my database
.  I guess the "select" could be made into a pg_fkeys view.  What do
people think...


Just a note.  I used PLPERL because the fkey data is stored in a BYTEA
data field and other then a "C" function PLPERL works fine for me...


Let me know if it works for you..
Jim


--
-- I called this function "j" during development and never changed
-- it.
--
CREATE FUNCTION j(bytea,varchar) RETURNS text AS '


@data = split(/\\\\000/, $_[0]);

$a = $data[0] if $_[1] eq "FKNAME";
$a = $data[1] if $_[1] eq "FTAB";
$a = $data[2] if $_[1] eq "TTAB";
$a = join(",",(@data)[4,6,8,10,12,14]) if $_[1] eq "FCOLS";
$a = join(",",(@data)[5,7,9,11,13,15]) if $_[1] eq "TCOLS";

$a =~ s/,+$//g;
return $a;
' LANGUAGE 'plperl';



select  a.tgconstrname,
        j(tgargs,'FTAB'::varchar) || '(' || j(tgargs,'FCOLS'::varchar)
|| ')' as from,
        j(tgargs,'TTAB'::varchar) || '(' || j(tgargs,'TCOLS'::varchar)
|| ')' as references
,
        cd as "cascade_delete",
        cu as cascade_update
from ( pg_trigger a left join
(select tgconstrname,'Y' as cd from pg_trigger where tgfoid = 1646) b on
(a.tgconstrname =
b.tgconstrname) )
left join
(select tgconstrname,'Y' as cu from pg_trigger where tgfoid = 1647) c on
(a.tgconstrname =
b.tgconstrname)
where
        tgfoid = 1644
        and
        tgisconstraint;




>
> A table of lookup codes has many tables which reference it via
foreign-key
> declarations, and I want to be able to merge two codes into one.
>
> For example lets say we have a CUSTOMER table and it uses a lookup
'code'
> field from the CUSTOMER_TYPE table.
>
> create table customer (
>     ...
>     typeCode text not null,
>     ...
> );
>
> create table customerType (
>     code text not null,
>     ...
>     constraint pk primary key (code)
> );
>
> alter table customer add constraint fk
>  foreign key (typeCode)
>  references customerType(code)
>  on update cascade;
>
> Everything will work just fine if we change the value of a code in the
> customerType table the values should propagate through to the
customers.
> But, if we have two codes in the code table, say 'wholesale' and
> 'distributor' and decide that the distributor type is no longer
needed, we
> need to set all customers (and about a dozen other tables) that were
> distributor to wholesale.  Although updating the entry of
'distributor' to
> 'wholesale' would update all the foreign references we can't since
there is a
> primary key on code in the customerType table.
>
> The best way I can think of doing this is to write a procedure which
will
> lock the old code in the code table, search the data dictionary for
all
> dependents, loop through each of them and construct/execute dynamic
update
> queries to alter the appropriate dependent records, and then deletes
the old
> code from the code table (this is how we did it in oracle).
>
> Anyone have a better approach?
>
> Assuming this is the right way to do this can someone advise me
where/how to
> extract the required data from the pg_xxx tables?
>
> thanks,
> Mike.
>
>
> ===================
> Mike Finn
> Tactical Executive Systems
> mike.finn@tacticalExecutive.com
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>