i am currently migrating a db from Oracle to Pg and in some of my triggers i use variables, i.e. i have some thing like this:
[Oracle version]
CREATE OR REPLACE TRIGGER "USERACTION_VIEW" INSTEAD OF INSERT ON "USERACTION_VIEW" DECLARE
UserActionID INTEGER;
UserActionDataID INTEGER;
begin
SELECT USERACTION_SQ.nextval into UserActionID FROM DUAL;
SELECT USERACTIONDATA_SQ.nextval into UserActionDataID FROM DUAL;
INSERT INTO USERACTION
(
UserActionID,
LocalID,
UserActionTypeID,
ActionDate
)
VALUES
(
UserActionID,
:new.LocalID,
:new.UserActionTypeID,
SYSDATE
);
INSERT INTO USERACTIONDATA
(
UserActionDataID,
UserActionID,
AccountID,
CourseRecordID,
CourseRecordPaymentID,
StudentID,
CourseRecordTestID,
CourseRecordLessonID
)
VALUES
(
UserActionDataID,
UserActionID,
:new.AccountID,
:new.CourseRecordID,
:new.CourseRecordPaymentID,
:new.StudentID,
:new.CourseRecordTestID,
:new.CourseRecordLessonID
);
end;
so basically i have two variables here, UserActionID and UserActionDataID. so when i am porting to PostgreSQL i will need to make this a rule since Pg triggers only work for tables. The problem though is that i don't know how to use variabvles in Pg rules. Any ideas? Is it possible at all?
Thanks.