CODEWITHSUNDEEP
oracle-databasepostgresql
Happy Coder
Happy Coder

Reputation: 1423

Oracle to PostgreSQL translate

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;

Upvotes: 1

Views: 257

Answers (1)

user330315
user330315

Reputation:

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();

Upvotes: 4

Related Questions