CODEWITHSUNDEEP
postgresqltriggersauto-incrementsequences
ComosGuy
ComosGuy

Reputation: 81

Autoincrement field with trigger and sequence - Check if ID is in the statement [Postgres]

I´ve got this sample from another thread and it fits perfectly on what I need to know.

CREATE SEQUENCE AlimentosSequencia;

CREATE TABLE alimento (
  id integer NOT NULL PRIMARY KEY
 ,name VARCHAR(255));

 CREATE OR REPLACE FUNCTION AlimentoFuncion()
 RETURNS "trigger" AS
 $BODY$
 BEGIN
   New.id:=nextval('AlimentosSequencia');
   Return NEW;
 END;
 $BODY$
 LANGUAGE 'plpgsql' VOLATILE;

 CREATE TRIGGER AlimentosTrigger
 BEFORE INSERT
 ON alimento
 FOR EACH ROW
 EXECUTE PROCEDURE AlimentoFuncion();

 INSERT INTO alimento (name) VALUES ('lemon');

This is working fine! But I have to complement the function AlimentoFunction.

Is there any way to check IN THE FUNCTION if my insert statement is placing an ID?

Example:

INSERT INTO alimento (name) VALUES ('lemon');

Ok this, works... and If I do that:

INSERT INTO alimento (id, name) VALUES (299, 'lemon');

My function must check if the sequence has to be used.

I know I can set the default value of ID to be the sequence on the table creation, but I can't modify that.. I just can change the trigger function.

Upvotes: 0

Views: 2659

Answers (1)

user330315
user330315

Reputation:

Something like:

if new.id is NULL then 
   New.id:=nextval('AlimentosSequencia');
end if;

Upvotes: 1

Related Questions