Обсуждение: 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