Thread: Referential cascade technique
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
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
>
>
On Mon, 23 Jul 2001, Jim Buttafuoco wrote:
> 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...
The following was posted to the list a while ago, compliments of Michael
Fork:
SELECT pt.tgargs, pt.tgnargs, pt.tgdeferrable, pt.tginitdeferred,
pg_proc.proname, pg_proc_1.proname FROM pg_class pc,
pg_proc pg_proc, pg_proc pg_proc_1, pg_trigger pg_trigger,
pg_trigger pg_trigger_1, pg_proc pp, pg_trigger pt
WHERE pt.tgrelid = pc.oid AND pp.oid = pt.tgfoid
AND pg_trigger.tgconstrrelid = pc.oid
AND pg_proc.oid = pg_trigger.tgfoid
AND pg_trigger_1.tgfoid = pg_proc_1.oid
AND pg_trigger_1.tgconstrrelid = pc.oid
AND ((pc.relname= '<< TABLENAME >>>')
AND (pp.proname LIKE '%%ins')
AND (pg_proc.proname LIKE '%%upd')
AND (pg_proc_1.proname LIKE '%%del')
AND (pg_trigger.tgrelid=pt.tgconstrrelid)
AND (pg_trigger_1.tgrelid = pt.tgconstrrelid));
This will show all foreign keys on a table.
> 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...
[snip]
--
Dominic J. Eidson
"Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/
Hello! Help me please. What i'm doing wrong? I just typed 'make' in ../src/test/examples directory, and got such a messages: /tmp/ccMibKh6.o(.text+0x1de): undefined reference to `PQexec' /tmp/ccMibKh6.o(.text+0x1e6): undefined reference to `PQclear' /tmp/ccMibKh6.o(.text+0x1f4): undefined reference to `PQexec' /tmp/ccMibKh6.o(.text+0x1fc): undefined reference to `PQclear' /tmp/ccMibKh6.o(.text+0x205): undefined reference to `PQfinish' collect2: ld returned 1 exit status make: *** [testlibpq] Error 1 Thanks for any suggestions. Igor
* Igor <dbmanager@osb368.nnov.ru> [010724 03:15]: > Hello! > > Help me please. What i'm doing wrong? I just typed 'make' > in ../src/test/examples directory, and got such a messages: > > /tmp/ccMibKh6.o(.text+0x1de): undefined reference to `PQexec' > /tmp/ccMibKh6.o(.text+0x1e6): undefined reference to `PQclear' > /tmp/ccMibKh6.o(.text+0x1f4): undefined reference to `PQexec' > /tmp/ccMibKh6.o(.text+0x1fc): undefined reference to `PQclear' > /tmp/ccMibKh6.o(.text+0x205): undefined reference to `PQfinish' > collect2: ld returned 1 exit status > make: *** [testlibpq] Error 1 > you aren't including -lpq. Larry > > > Thanks for any suggestions. > > Igor > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
while the below query does product all foreign keys, my PLPERL/query looks much better... > On Mon, 23 Jul 2001, Jim Buttafuoco wrote: > > > 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... > > The following was posted to the list a while ago, compliments of Michael > Fork: > > SELECT pt.tgargs, pt.tgnargs, pt.tgdeferrable, pt.tginitdeferred, > pg_proc.proname, pg_proc_1.proname FROM pg_class pc, > pg_proc pg_proc, pg_proc pg_proc_1, pg_trigger pg_trigger, > pg_trigger pg_trigger_1, pg_proc pp, pg_trigger pt > WHERE pt.tgrelid = pc.oid AND pp.oid = pt.tgfoid > AND pg_trigger.tgconstrrelid = pc.oid > AND pg_proc.oid = pg_trigger.tgfoid > AND pg_trigger_1.tgfoid = pg_proc_1.oid > AND pg_trigger_1.tgconstrrelid = pc.oid > AND ((pc.relname= '<< TABLENAME >>>') > AND (pp.proname LIKE '%%ins') > AND (pg_proc.proname LIKE '%%upd') > AND (pg_proc_1.proname LIKE '%%del') > AND (pg_trigger.tgrelid=pt.tgconstrrelid) > AND (pg_trigger_1.tgrelid = pt.tgconstrrelid)); > > This will show all foreign keys on a table. > > > > 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... > > [snip] > > -- > Dominic J. Eidson > "Baruk Khazad! Khazad ai-menu!" - Gimli > ------------------------------------------------------------------------------- > http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >
> 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...
I think what you've done is quite good. I have had to adapt what you've done
since I really don't want to use perl. I want to minimize the dependencies
the the db has so I've opted to figure out how to do it in pgplsql (and yes
those byte arrays a pain).
One assumption I have made is that the foreign key references are all single
field (not compound key). This works in my system since this whole issue is
really for code tables, where we migrate the actual data to the referring
table so as to avoid a ton of joins for simple little code lookups.
I am including the whole shebang here in case it is of use to others, or
simple as a non-trivial example of plpgsql (which I have just had a crash
course in!).
-- a view to display foreign key references
-- BUG: assumes that all fk relationships are sigle key
drop view foreignKeyReference;
create view foreignKeyReference as
select
--field order is 0. procName
-- 1. referringTable
-- 2. localTable
-- 3. UNSPECIFIED
-- 4. referringField
-- 5. localField
proc.proname as proc,
trig.tgnargs = 6 as isSingleFieldKey,
extractParmFromBytea(trig.tgargs, 0) as name,
extractParmFromBytea(trig.tgargs, 2) as tableName,
extractParmFromBytea(trig.tgargs, 5) as fieldName,
extractParmFromBytea(trig.tgargs, 1) as referringTable,
extractParmFromBytea(trig.tgargs, 4) as referringField
from
pg_class class,
pg_trigger trig,
pg_proc proc
where
class.relname not like 'pg_%' and class.relkind = 'r'
and trig.tgrelid = class.oid
and trig.tgisconstraint = true
and trig.tgfoid = proc.oid
--and proc.proname like 'RI_FKey%_del'
order by
class.relname, trig.tgname
;
-- Get the Nth parm from a parameter set stored as null delimited
-- bytea as a string. Parameters are numbered from 0
drop function extractParmFromBytea(bytea, int);
create function extractParmFromBytea(bytea, int) returns text as '
declare
bparm alias for $1; --bytea parameters
parmIdx alias for $2;
parmCount int = 0;
charCount int = 0;
c int;
parm text = '''';
begin
while parmCount < parmIdx loop
c := get_byte(bparm, charCount);
charCount := charCount + 1;
if c = 0 then
parmCount := parmCount + 1;
end if;
end loop;
while parmCount = parmIdx loop
c := get_byte(bparm, charCount);
charCount := charCount + 1;
if c = 0 then
parmCount := parmCount + 1;
else
parm := parm || chr(c);
end if;
end loop;
return parm;
end;
' language 'plpgsql';
-- For a given table (localTable) and field (localField) which are known
-- to be referred to via one or more foregin key relationships, update all
-- the dependant foreign references from oldValue to newValue. This will
-- effectively move the dependencies from one record in localTable to another
-- record in localTable.
-- BUG: assumes that all fk relationships are sigle key
drop function moveDependants(text,text,text,text);
create function moveDependants(text,text,text,text) returns boolean as '
declare
--parameters
localTable alias for $1;
localField alias for $2;
oldValue alias for $3;
newValue alias for $4;
--translation from bytea to parms
referringTable text;
referringField text;
--main part
query text;
fks record;
rc int;
begin
--lock the source oldValue exclusively
query := '' select null''
|| '' from ''
|| localTable
|| '' where ''
|| localField || '' = '' || quote_literal(oldValue)
|| '' for update ''
;
execute query;
--lock the source newValue exclusively
query := '' select null''
|| '' from ''
|| localTable
|| '' where ''
|| localField || '' = '' || quote_literal(newValue)
|| '' for update ''
;
execute query;
get diagnostics rc = ROW_COUNT;
if rc <= 0 then
raise exception ''newValue of % does not exist in %.%'',
newValue, localTable, localField;
end if;
--find the parameters for the del triggers
query := '' select ''
|| '' tgargs ''
|| '' from ''
|| '' pg_class class, ''
|| '' pg_trigger trig, ''
|| '' pg_proc proc ''
|| '' where ''
|| '' class.relname = '' ||
quote_literal(lower(localTable))
|| '' and class.relkind = ''''r'''' ''
|| '' and trig.tgrelid = class.oid ''
|| '' and trig.tgisconstraint = true ''
|| '' and trig.tgfoid = proc.oid ''
|| '' and proc.proname like ''''RI_FKey%_del'''' ''
;
--field order is 0. procName
-- 1. referringTable
-- 2. localTable
-- 3. UNSPECIFIED
-- 4. referringField
-- 5. localField
for fks in execute query loop
if lower(localField) = extractParmFromBytea(fks.tgargs, 5) then
--okay this reference is for localTable and localField
--so update the referring values from oldValue to newValue
query := '' update ''
|| extractParmFromBytea(fks.tgargs, 1)
|| '' set ''
|| extractParmFromBytea(fks.tgargs, 4) || '' =''
|| quote_literal(newValue)
|| '' where ''
|| extractParmFromBytea(fks.tgargs, 4) || '' =''
|| quote_literal(oldValue)
;
--raise notice ''processing %'', query;
execute query;
end if;
end loop;
return true; --bogus return value
end;
' language 'plpgsql';
-- an example
-- believe it or not the postal abbreviation for Quebec
-- is actually 'QC'... its true, go figure!
/*
begin
select moveDependants('codeProvinceState','code','PQ','QC');
delete from codeProvinceState
where code = 'Q.C.';
commit;
*/
===================
Mike Finn
Tactical Executive Systems
mike.finn@tacticalExecutive.com