Re: Referential cascade technique

> 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(dot)finn(at)tacticalExecutive(dot)com