Postgres-为表分配自定义安全性

Question

我创建了一个test_seq，我想将我的安全性分配给一个表。

CREATE SEQUENCE test_seq;

和实施：

CREATE SEQUENCE drop_permission_seq;
CREATE OR REPLACE FUNCTION before_insert_drop_permission() RETURNS trigger AS $$
DECLARE
_id  TEXT;
BEGIN

PERFORM 
CASE
WHEN length(nextval('drop_permission_seq')::TEXT) < 2 THEN _id = 'DP-000000'||(nextval('drop_permission_seq')::TEXT)
WHEN length(nextval('drop_permission_seq')::TEXT) < 3 THEN _id = 'DP-00000'||(nextval('drop_permission_seq')::TEXT)
WHEN length(nextval('drop_permission_seq')::TEXT) < 4 THEN _id = 'DP-0000'||(nextval('drop_permission_seq')::TEXT)
WHEN length(nextval('drop_permission_seq')::TEXT) < 5 THEN _id = 'DP-000'||(nextval('drop_permission_seq')::TEXT)
WHEN length(nextval('drop_permission_seq')::TEXT) < 6 THEN _id = 'DP-00'||(nextval('drop_permission_seq')::TEXT)
WHEN length(nextval('drop_permission_seq')::TEXT) < 7 THEN _id = 'DP-0'||(nextval('drop_permission_seq')::TEXT)
ELSE _id = 'DP-'||(nextval('drop_permission_seq')::TEXT)
END ;
-- Now assign the order id and continue with the insert
NEW.drop_permission_name = _id;
RETURN NEW;
END; $$ LANGUAGE plpgsql;
CREATE TRIGGER assign_drop_permission_key
BEFORE INSERT ON public.drop_permission
FOR EACH ROW EXECUTE PROCEDURE before_insert_drop_permission();

但每次我尝试插入时，我都会收到错误：

错误：列中的空值"drop_permission_name"违反非null约束

为什么不插入主键？我该如何解决？

Answer 1

您想要

ALTER TABLE ... ALTER col
SET DEFAULT to_char(nextval('drop_permission_seq'), 'DP-0000000');

Answer 2

您可以使用序列下一个值作为id字段的默认值：

ALTER TABLE users ALTER COLUMN id SET DEFAULT nextval('test_seq');