Postgres检查约束可能引用当前行中的列,但不清楚如何引用整个record
以使用以record
为参数的函数
例如,统计记录中非空条目数量的通用检查约束:
-- Function that counts the number of non-null entries in a row
create function count_non_null(rec record) returns int as $$
begin
return count(v) from json_each(row_to_json(rec)) x(k_, v) where json_typeof(x.v) <> 'null';
end;
$$ language plpgsql immutable strict;
-- Check constraint asserting that only 3 values may be set on each row
alter table some_table add constraint ck_three_key check (
count_non_null(CURRENT_ROW) = 3 -- invalid
);
在此上下文中不允许使用CURRENT_ROW。你知道如何将当前行传递给检查约束内的函数吗?
使用表名:
alter table some_table
add constraint ck_three_key
check (count_non_null(some_table) = 3);