sb_get_foreign_keys (public)

 sb_get_foreign_keys table_name

Defined in packages/schema-browser/tcl/schema-browser-procs-postgresql.tcl

Build a list describing all foreign keys on table_name and their actions. We ignore MATCH conditions because Oracle doesn't support them, therefore, OpenACS doesn't use them. Same is true of SET NULL and SET DEFAULT actions hung on ON DELETE/ON UPDATE subclauses, but since Oracle *does* support CASCADE as an action I had figure out how to grab this info from the system catalog anyway. This code is *horribly* convoluted, mostly a result of the non-obvious way that the needed information is organized in the PG system catalogs. g Feel free to clean this up if you want!

Parameters:

table_name

Author:

Don Baccus, though he hates to admit to writing such ugly code <dhogaza@pacifier.com>

Partial Call Graph (max 5 caller/called nodes):

Testcases:

No testcase defined.

Source code:

    set complex_foreign_keys [list]
    db_foreach schema_browser_get_referencess {
         select t.tgargs as constraint_args,
             conname as constraint_name,
             'NOACTION' as action,
             'CHECK' as trigger_kind,
             r1.relname as refer_table,
             t.oid as oid,
             0 as sort_key
         from
             pg_trigger t,
             pg_class r,
             pg_class r1,
             pg_constraint c,
             pg_proc p
         where
             lower(r.relname) = lower(:table_name) and
             r.oid = t.tgrelid and
             r1.oid = t.tgconstrrelid and
             t.tgfoid = p.oid and
             c.conrelid  = r.oid and
             p.proname = 'RI_FKey_check_ins'
         union all
         select t.tgargs as constraint_args,
             conname as constraint_name,
             case
               when p.proname like '%noaction%' then 'NOACTION'
               when p.proname like '%cascade%' then 'CASCADE'
               when p.proname like '%setnull%' then 'SET NULL'
               when p.proname like '%setdefault%' then 'SET DEFAULT'
             end as action,
             case
               when p.proname like '%upd' then 'ON UPDATE'
               when p.proname like '%del' then 'ON DELETE'
             end as trigger_kind,
             r1.relname as refer_table,
             t.oid as oid,
             1 as sort_key
         from
             pg_trigger t,
             pg_class r,
             pg_class r1,
             pg_constraint c,
             pg_proc p
         where
             lower(r.relname) = lower(:table_name) and
             r.oid = t.tgconstrrelid and
             r1.oid = t.tgrelid and
             t.tgfoid = p.oid and
             c.conrelid  = r.oid and
             not p.proname like 'RI%_check_%'
         order by oid, sort_key
    } {
        set one_ri_datum [list]
        set arg_start 0
        while { $constraint_args ne "" } {
            set arg_end [expr {[string first "\\000" $constraint_args] - 1}]
            lappend one_ri_datum [string range $constraint_args $arg_start $arg_end]
            set constraint_args [string range $constraint_args $arg_end+5 end]
        }
        switch $trigger_kind {
            CHECK {
                 if { [info exists foreign_key_sql] } {
                     if { [info exists arg_count] && $arg_count == 1 } {
                         set references($on_var) $foreign_key_sql
                     } else {
                         lappend complex_foreign_keys $foreign_key_sql
                     }
                 }
                 if { $constraint_name eq "<unnamed>" } {
                     set foreign_key_sql ""
                 } else {
                     set foreign_key_sql "CONSTRAINT $constraint_name "
                 }
                 set on_var_part ""
                 set refer_var_part ""
                 set sep ""
                 set arg_count 0
                 foreach { on_var refer_var } [lrange $one_ri_datum 4 end] {
                     append refer_var_part "$sep$refer_var"
                     append on_var_part "$sep$on_var"
                     set sep ", "
                     incr arg_count
                 }
                 if { $arg_count > 1 } {
                     append foreign_key_sql "FOREIGN KEY ($on_var_part) "
                 }
                 append foreign_key_sql "REFERENCES <a href=\"index?table_name=$refer_table\">$refer_table</a> ($refer_var_part)"
            }
            default {
                if { $action ne "NOACTION" } {
                    append foreign_key_sql " $trigger_kind $action"
                }
            }
        }
    }
    if { [info exists foreign_key_sql] } {
        if { $arg_count == 1 } {
            set references($on_var) $foreign_key_sql
        } else {
            lappend complex_foreign_keys $foreign_key_sql
        }
    }
    return [list [array get references] $complex_foreign_keys]

XQL Not present:

Generic, PostgreSQL, Oracle