I am migrating Oracle DLL to PostgreSQL, I am having an issue translate the following piece of code to PostgreSQL
-- Generate ID using sequence and trigger
CREATE SEQUENCE partner_seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER partner_seq_tr
BEFORE INSERT ON partner FOR EACH ROW
WHEN (NEW.idPartner IS NULL)
BEGIN
SELECT partner_seq.NEXTVAL INTO :NEW.idPartner FROM DUAL;
END;
/
-- CREATE UNIQUE INDEX partner_idxName ON partner (Name);
-- COMMIT;
You don't actually need a trigger in Postgres for that. Simply declare the column as
idpartner integer generated always as identity
And Postgres will use the (automatically created) sequence automatically if the column is not specified as a target column in the INSERT statement.
Alternatively, if you really want a trigger:
create sequence partner_seq;
create function assign_partner_id()
returns trigger
as
$$
begin
if new.idpartner is null then
new.idpartner := nextval('partner_seq');
end if;
return new;
end;
$$
language plpgsql;
create trigger partner_seq_trg
before on partner insert on each row
execute procedure assign_partner_id();