Re: Referential cascade technique - Mailing list pgsql-general

> 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