Re: Replacement for tgisconstraint? (Upgrade from 8.4 to 9.0.5) - Mailing list pgsql-general

 Not being the author of that view, I confess some ignorance of pg internals, and just what the intended nuance was.

As a little more explanation, the view is meant to list all the tables that have a trigger ending in _alert_notify, as created per this function:

CREATE OR REPLACE FUNCTION alert_notify_enable(varchar,varchar) RETURNS boolean AS $$
        if {[info exists 1]} {
            set TABLE $1
        } else {
            elog ERROR "no table passed to alert_notify()"
            return false
        }
        if {[info exists 2]} {
            set CUSTOM_COLUMN  $2
        } else {
            set CUSTOM_COLUMN ""
        }
        set cre_exec  "CREATE TRIGGER ${TABLE}_alert_notify
        AFTER INSERT OR UPDATE OR DELETE ON ${TABLE}
        FOR EACH ROW EXECUTE PROCEDURE table_alert_notify(${CUSTOM_COLUMN})"
        spi_exec $cre_exec
        return true
$$ LANGUAGE pltcl;

(The second view, about table_logs, is conceptually similar).

Here's the slightly more readable source for the view:


CREATE OR REPLACE VIEW alert_notify_enabled_objects AS

SELECT REPLACE(cc.relname,'tbl_','') AS alert_object_code,
        INITCAP(REPLACE(REPLACE(cc.relname,'tbl_',''),'_',' ')) AS description
FROM pg_catalog.pg_trigger t
     LEFT JOIN pg_catalog.pg_class cc ON ( t.tgrelid = cc.oid )
WHERE t.tgname ~ '_alert_notify$'
        AND (NOT tgisconstraint  OR NOT EXISTS
                   (SELECT 1 FROM pg_catalog.pg_depend d
                             JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid)
                        WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f')
        );

If that clarifies the intention, please let me know!  Also, what about question #2--is there an easy/built-in way to edit the pg_upgrade_dump_db.sql and continue the postgresql-upgrade process?

Thanks!

Ken