序列相关操作(postgresql)
序列创建
create sequence public.test_sequece
increment by 1
minvalue 10000000
maxvalue 9999999999
start with 10000000
cache 1;
序列查询
currval of sequence “test_sequence” is not yet defined in this session
查阅参考资料,发现错误原因是要查当前值,必须先取一次next:执行以下语句,执行成功:
select nextval(‘test_sequence’);
select currval(‘test_sequence’)
序列值增长至指定值(用于解决主键冲突问题)
--创建函数
CREATE OR REPLACE FUNCTION public.sequence_reset()
RETURNS void AS
$BODY$
DECLARE
indexnum int ;
begin
for ind in 1..2 loop
select nextval('test_sequence') into indexnum;
end loop ;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.sequence_reset()
OWNER TO testdata;
--执行函数
select sequence_reset();
--删除函数(执行完自定义函数后一般给他删除掉,不影响其他值)
drop function public.sequence_reset();
模糊查询自定义函数
--查看自定义的函数信息
SELECT
pg_proc.proname AS "函数名称",
pg_type.typname AS "返回值数据类型",
pg_proc.pronargs AS "参数个数"
FROM
pg_proc
JOIN pg_type
ON (pg_proc.prorettype = pg_type.oid)
WHERE
-- pg_type.typname != 'void'
pg_proc.proname like '%sequence_reset%'
查询数据库中有哪些序列
--查看数据库中有哪些序列
--r =普通表, i =索引,S =序列,v =视图,m =物化视图, c =复合类型,t = TOAST表,f =外部表
select * from pg_class where relkind='S'
```