postgresql 在 Postgres 中使用触发器和序列创建自动增量字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11125419/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create autoincrement field with trigger and sequence in Postgres
提问by jgiunta
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)
我正在尝试使用触发器和序列创建自动增量字段（如 SERIAL）。我知道只能在字段上使用序列或 SERIAL 类型，但我必须使用这两种方法（触发器和序列）来解决这个问题
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.
我尝试了这种组合，但不起作用，表 alimento 只有两个字段，整数 id（带有触发器和序列的自动增量）和 varchar 名称。
Any suggestion ?
有什么建议吗？
Thanks
谢谢
回答by doctore
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 idfield as serial type, that it would create the sequence automatically.
另一种可能性是将id字段声明为串行类型，它会自动创建序列。
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.
它可以毫无问题地工作。
