Geek asks and answers

Creating an auto-increment field with a trigger and sequence in Postgres

I am trying to create an auto-increment field (e.g. SERIAL) using a trigger and sequence. I know that only a field can use a sequence or type SERIAL, but I have to solve this using both methods (triggers and locks)

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 to do this combination, but dosen't works, the alimento table has only two fields: integer id (auto-increment with trigger and sequence) and name varchar.

Any suggestion?

thank

+5
source share
1 answer

As other users have said, you do not need to use a trigger. You can declare a table as follows:

CREATE SEQUENCE AlimentosSequencia;

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

And when you insert a new entry:

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

id , .

UPDATE: , . , ? :

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

.

+6

More articles:


All Articles