Re: Converting IBM DB2 TRIGGERs to PostgreSQL

Alan,

> Part of my challenge is to somehow convert the following (sample)
> TRIGGER statements that work just fine under DB2.

Easy.  Read the part in the "Procedural Languages" section in the online docs
on "PL/pgSQL Triggers"

To do what you want .....

CREATE FUNCTION tf_alien_log () RETURNS TRIGGER AS '
DECLARE op_var TEXT;
    create_date TIMESTAMP;
BEGIN
    IF TG_OP = ''UPDATE'' THEN
        op_var := ''UPD'';
        create_date := NEW.created;
    ELSE
        op_var := ''INS'';
        create_date := CURRENT_TIMESTAMP;
    END IF;

    INSERT INTO alien.country_log
    VALUES (NEW.countryid, NEW.fullname, NEW.code, NEW.undefined,
        NEW.markfordelete, NEW.userid,
        create_date, CURRENT_TIMESTAMP, op_var);

    RETURN NEW;
END; '  LANGUAGE 'plpgsql';

CREATE TRIGGER tg_alien_log AFTER INSERT OR UPDATE ON alien.country
FOR EACH ROW EXECUTE tg_alien_log();

Easy, neh?  Plus in the future it will become possible to write triggers in
other langauges, such as TCL and Perl.

BTW, the "BEGIN ATOMIC" is superfluous in PostgreSQL; triggers are atomic by
definition.

--
Josh Berkus
Aglio Database Solutions
San Francisco