Re: Translating Oracle CREATE TRIGGER statement

Scott Cain <scain@safehooks.org> writes:
> I have no experience with Oracle or with triggers in general, but I've
> been given several CREATE statements to base my Postgres schema on, but
> I don't know how to translate the trigger statements.  Here is an
> example Oracle create:

> CREATE OR REPLACE TRIGGER fgroup_gid_ai
> BEFORE INSERT ON fgroup
> FOR EACH ROW WHEN (new.gid IS NULL OR new.gid = 0)
> BEGIN
>     SELECT fgroup_gid_sq.nextval INTO :new.gid FROM dual;
> END;

You need to convert the action (and in this case the condition as well)
into a plpgsql function.  Something like this (untested) code:

create function fgroup_insert_trig() returns trigger as '
begin
if new.gid IS NULL OR new.gid = 0 then
  new.gid := nextval(''fgroup_gid_sq'');
end if;
return new;
end' language plpgsql;

create trigger fgroup_gid_ai before insert on fgroup
for each row execute procedure fgroup_insert_trig();

            regards, tom lane