triggers cant see functions - Mailing list pgsql-general

hello all,
    I dont know if the functions actually work yet,
 but they create and drop. the triggers break cause it
 appears that they cant find the functions. attached are
 the functions and triggers scripts and the errors from
 when i "run" the triggers script. Yes i ran the
 functions fist before the triggers...

    I am new to pltcl, and if you can tell what the
 functions are supposed to do, that is all i want. Using
 tcl cause it appears to be more battle tested than the
 other options.

    A friend directed me to the post where tom lane
 directed someone to .c files in the contrib directory.
 I couldnt make sence out of them as fast as i would have
 liked, and seems to scary to test that way *shrug*. I
 just need to get the logging into changelog working, and
 am having alot of trouble. any help in getting this to
 work would be great.

thanks,
chris humphries

-- -----------------------
-- - Functions for ACMIS -
-- -----------------------

--
-- log_insert()
--
-- Inserts a record in the ChangeLog table for an INSERT
--
DROP FUNCTION log_insert(varchar);
CREATE FUNCTION log_insert(varchar) RETURNS OPAQUE AS '
    set log_entry ""

    foreach colname [array names TG_relatts] {
        set logentry "$logentry::$colname:$NEW($colname)"
    }

    set query  "INSERT INTO changelog (changelogtable, changelogdesc, changeloguser, changelogaction) VALUES ([quote
$1],[quote $logentry], [quote $NEW(lastuser)], [quote \"INSERT\"])" 
    spi_exec $query

    elog NOTICE "*** log_insert: ''$query''"

    return NEW
' language 'pltcl';

--
-- log_delete()
--
-- Inserts a record in the ChangeLog table for an DELETE,
-- _AND_ deletes the record from tablename.
--
DROP FUNCTION log_delete(varchar, int, varchar);
CREATE FUNCTION log_delete(varchar, int, varchar) RETURNS OPAQUE AS '
    set log_entry ""
    set query ""

    set query "DELETE from $1 where $2 = $OLD($2)"
    spi_exec $query
    elog NOTICE "*** log_delete: $query"
    set query ""

    foreach colname [array names TG_relatts] {
        set logentry "$logentry::$colname:$OLD($colname)"
    }

    set query  "INSERT INTO changelog (changelogtable, changelogdesc, changeloguser, changelogaction) VALUES ([quote
$1],[quote $logentry], [quote $3], [quote \"DELETE\"])" 
    spi_exec $query
    elog NOTICE "*** log_delete: $query"

    return OLD
' language 'pltcl';

--
-- log_update()
--
-- Inserts a record in the ChangeLog table for an UPDATE
--
DROP FUNCTION log_update(varchar);
CREATE FUNCTION log_update(varchar) RETURNS OPAQUE AS '
    set log_entry ""

    foreach colname [array names TG_relatts] {
        #if {!string equal $OLD($colname) $NEW($colname)} {
        if {$OLD($colname) != $NEW($colname) {
            set logentry "$logentry::$colname:$NEW($colname)"
        }
    }

    set query  "INSERT INTO changelog (changelogtable, changelogdesc, changeloguser, changelogaction) VALUES ([quote
$1],[quote $logentry], [quote $NEW(lastuser)], [quote \"INSERT\"])" 
    spi_exec $query

    elog NOTICE "*** log_insert: $query"

    return NEW
' language 'pltcl';

ERROR:  DropTrigger: there is no trigger job_log_insert_trigger on relation job
ERROR:  CreateTrigger: function log_insert() does not exist
ERROR:  DropTrigger: there is no trigger job_log_update_trigger on relation job
ERROR:  CreateTrigger: function log_update() does not exist
ERROR:  DropTrigger: there is no trigger employee_log_insert_trigger on relation employee
ERROR:  CreateTrigger: function log_insert() does not exist
ERROR:  DropTrigger: there is no trigger employee_log_update_trigger on relation employee
ERROR:  CreateTrigger: function log_update() does not exist
ERROR:  DropTrigger: there is no trigger education_log_insert_trigger on relation education
ERROR:  CreateTrigger: function log_insert() does not exist
ERROR:  DropTrigger: there is no trigger education_log_update_trigger on relation education
ERROR:  CreateTrigger: function log_update() does not exist
ERROR:  DropTrigger: there is no trigger training_log_insert_trigger on relation training
ERROR:  CreateTrigger: function log_insert() does not exist
ERROR:  DropTrigger: there is no trigger training_log_update_trigger on relation training
ERROR:  CreateTrigger: function log_update() does not exist
NOTICE:  identifier "certification_log_insert_trigger" will be truncated to "certification_log_insert_trigge"
ERROR:  DropTrigger: there is no trigger certification_log_insert_trigge on relation certification
NOTICE:  identifier "certification_log_insert_trigger" will be truncated to "certification_log_insert_trigge"
ERROR:  CreateTrigger: function log_insert() does not exist
NOTICE:  identifier "certification_log_update_trigger" will be truncated to "certification_log_update_trigge"
ERROR:  DropTrigger: there is no trigger certification_log_update_trigge on relation certification
NOTICE:  identifier "certification_log_update_trigger" will be truncated to "certification_log_update_trigge"
ERROR:  CreateTrigger: function log_update() does not exist
NOTICE:  identifier "requirementswaiver_log_insert_trigger" will be truncated to "requirementswaiver_log_insert_t"
ERROR:  DropTrigger: there is no trigger requirementswaiver_log_insert_t on relation requirementswaiver
NOTICE:  identifier "requirementswaiver_log_insert_trigger" will be truncated to "requirementswaiver_log_insert_t"
ERROR:  CreateTrigger: function log_insert() does not exist
NOTICE:  identifier "requirementswaiver_log_update_trigger" will be truncated to "requirementswaiver_log_update_t"
ERROR:  DropTrigger: there is no trigger requirementswaiver_log_update_t on relation requirementswaiver
NOTICE:  identifier "requirementswaiver_log_update_trigger" will be truncated to "requirementswaiver_log_update_t"
ERROR:  CreateTrigger: function log_update() does not exist
ERROR:  DropTrigger: there is no trigger cowarrant_log_insert_trigger on relation cowarrant
ERROR:  CreateTrigger: function log_insert() does not exist
ERROR:  DropTrigger: there is no trigger cowarrant_log_update_trigger on relation cowarrant
ERROR:  CreateTrigger: function log_update() does not exist


--
-- trigger for insert to job for change log
--
DROP TRIGGER job_log_insert_trigger on job;
CREATE TRIGGER job_log_insert_trigger
AFTER INSERT
ON job FOR EACH ROW
EXECUTE PROCEDURE log_insert('job');

--
-- trigger for update to job for change log
--
DROP TRIGGER job_log_update_trigger on job;
CREATE TRIGGER job_log_update_trigger
AFTER DELETE
ON job FOR EACH ROW
EXECUTE PROCEDURE log_update('job');

----

--
-- trigger for insert to employee for change log
--
DROP TRIGGER employee_log_insert_trigger on employee;
CREATE TRIGGER employee_log_insert_trigger
AFTER INSERT
ON employee FOR EACH ROW
EXECUTE PROCEDURE log_insert('employee');

--
-- trigger for update to employee for change log
--
DROP TRIGGER employee_log_update_trigger on employee;
CREATE TRIGGER employee_log_update_trigger
AFTER UPDATE
ON employee FOR EACH ROW
EXECUTE PROCEDURE log_update('employee');

----

--
-- trigger for insert to education for change log
--
DROP TRIGGER education_log_insert_trigger on education;
CREATE TRIGGER education_log_insert_trigger
AFTER INSERT
ON education FOR EACH ROW
EXECUTE PROCEDURE log_insert('education');

--
-- trigger for update to education for change log
--
DROP TRIGGER education_log_update_trigger on education;
CREATE TRIGGER education_log_update_trigger
AFTER UPDATE
ON education FOR EACH ROW
EXECUTE PROCEDURE log_update('education');

----

--
-- trigger for insert to training for change log
--
DROP TRIGGER training_log_insert_trigger on training;
CREATE TRIGGER training_log_insert_trigger
AFTER INSERT
ON training FOR EACH ROW
EXECUTE PROCEDURE log_insert('training');

--
-- trigger for update to training for change log
--
DROP TRIGGER training_log_update_trigger on training;
CREATE TRIGGER training_log_update_trigger
AFTER UPDATE
ON training FOR EACH ROW
EXECUTE PROCEDURE log_update('training');

----

--
-- trigger for insert to certification for change log
--
DROP TRIGGER certification_log_insert_trigger on certification;
CREATE TRIGGER certification_log_insert_trigger
AFTER INSERT
ON certification FOR EACH ROW
EXECUTE PROCEDURE log_insert('certification');

--
-- trigger for update to certification for change log
--
DROP TRIGGER certification_log_update_trigger on certification;
CREATE TRIGGER certification_log_update_trigger
AFTER UPDATE
ON certification FOR EACH ROW
EXECUTE PROCEDURE log_update('certification');

----

--
-- trigger for insert to requirementswaiver for change log
--
DROP TRIGGER requirementswaiver_log_insert_trigger on requirementswaiver;
CREATE TRIGGER requirementswaiver_log_insert_trigger
AFTER INSERT
ON requirementswaiver FOR EACH ROW
EXECUTE PROCEDURE log_insert('requirementswaiver');

--
-- trigger for update to requirementswaiver for change log
--
DROP TRIGGER requirementswaiver_log_update_trigger on requirementswaiver;
CREATE TRIGGER requirementswaiver_log_update_trigger
AFTER UPDATE
ON requirementswaiver FOR EACH ROW
EXECUTE PROCEDURE log_update('requirementswaiver');

----

--
-- trigger for insert to cowarrant for change log
--
DROP TRIGGER cowarrant_log_insert_trigger on cowarrant;
CREATE TRIGGER cowarrant_log_insert_trigger
AFTER INSERT
ON cowarrant FOR EACH ROW
EXECUTE PROCEDURE log_insert('cowarrant');

--
-- trigger for update to cowarrant for change log
--
DROP TRIGGER cowarrant_log_update_trigger on cowarrant;
CREATE TRIGGER cowarrant_log_update_trigger
AFTER UPDATE
ON cowarrant FOR EACH ROW
EXECUTE PROCEDURE log_update('cowarrant');

----