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