Thread: Translating Oracle CREATE TRIGGER statement

Hello,

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;

While I kind of get the idea of what it is trying to do, I don't know
how to implement it in Postgres. fgroup_gid_sq is a sequence, but I have
no idea what dual is. Any advice would be appreciated.

Thanks,
Scott

Hi all,
I have a table with 3mill records of which 500K need updating.  If I run:
update fmstitm set dist = 't' where fmstitm.fitem = dist_its.fitem

does it update the entire table or one record at a time...

in other words I run
select count(*) from fmstitm where dist = 't';

as the update is running and i get o results.  Is this normal?  The update
is taking a long time 1+ hours so far.
I just want to check that something might be wrong without any results
returned by the count(*) query.

-Chris

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