CODEWITHSUNDEEP
postgresql
jgiunta
jgiunta

Reputation: 721

Create autoincrement field with trigger and sequence in Postgres

i'm triying to create an autoincrement field (like SERIAL) using a trigger and sequence. I know that only can use a sequence or SERIAL type on field, but i must resolve this using both methods (triggers and secuences)

CREATE SEQUENCE AlimentosSequencia;

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

I try this combination but dosen't works, the table alimento has two fields only, integer id(the autoincrement with trigger and sequence) and the varchar name.

Any suggestion ?

Thanks

Upvotes: 7

Views: 30156

Answers (1)

user731136
user731136

Reputation:

As others users have told you, you don't need to use a trigger. You can declare the table like this:

CREATE SEQUENCE AlimentosSequencia;

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

And when you insert a new record:

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

Another possibility is declared the id field as serial type, that it would create the sequence automatically.

UPDATE: Ok, it's an exercise. Then I don't understand what's the problem? I have tested this code:

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

And it works without problems.

Upvotes: 11

Related Questions