PostgreSQL使用存储过程为插入的数据自动生成ID

 

数据库中经常需要插入纪录，为每条记录维持一个ID,在PostgreSQL中的方法为:

在表中插入ID

产生一个序列发生器！！

CREATE SEQUENCE serial START 101;

 

 

 

使用序列操作函数

Table 9.38. Sequence Functions

Function

Return Type

Description

currval(regclass)

bigint

Return value most recently obtained with nextval for specified sequence

lastval()

bigint

Return value most recently obtained with nextval for any sequence

nextval(regclass)

bigint

Advance sequence and return new value

setval(regclass, bigint)

bigint

Set sequence's current value

setval(regclass, bigint, boolean)

bigint

Set sequence's current value and is_called flag

以下为实例程序

nextval方法的参数可以直接用序列发生器的名称字符串

用一个bigint变量接受产生的序列号

REATE OR REPLACE FUNCTION add_student("name" text, age integer)

 RETURNS bigint AS

$BODY$DECLARE

s_no bigint;

BEGIN

s_no := nextval('serial');

insert into students values (s_no,$1,$2);

return s_no;

END;$BODY$