Create autoincrement field with trigger and sequence in Postgres

Question

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

You can just call nextval() on the associated sequence of a serial column for other purposes. No need to complicate things with a trigger. Aside from that, without error message, your question is just noise. Include it verbatim, please. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jun 20, 2012 at 18:09
You could set the nextval('AlimentosSequencia') as the default value for this column, even when it is integer. No need of triggers. — Szymon Lipiński
– Szymon Lipiński, Commented Jun 20, 2012 at 18:18
it's an exercise guys, i must doit using trigger and sequence, here is not evaluating the performance. Thanks — jgiunta
– jgiunta, Commented Jun 22, 2012 at 14:01

score 11 · Accepted Answer · 2012-06-23 07:32:11Z

11

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.

edited Jun 23, 2012 at 7:32

answered Jun 22, 2012 at 12:04

user731136

Sign up to request clarification or add additional context in comments.

1 Comment

jgiunta Over a year ago

The problem was when i declared de ID field ... :( !!!! But readng your answer i detect the problem. Thanks again

Collectives™ on Stack Overflow

Create autoincrement field with trigger and sequence in Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related