给定一个PostgreSQL表requests，该表具有一个名为的列status和一个约束，如下所示：
ALTER TABLE requests ADD CONSTRAINT allowed_status_types
CHECK (status IN (
'pending', -- request has not been attempted
'success', -- request succeeded
'failure' -- request failed
));
在这种情况下，psql我可以提取有关此约束的信息：
example-database=# \d requests
Table "public.example-database"
Column | Type | Modifiers
----------------------+-----------------------------+-------------------------------------------------------------------
id | integer | not null default nextval('requests_id_seq'::regclass)
status | character varying | not null default 'pending'::character varying
created_at | timestamp without time zone | not null
updated_at | timestamp without time zone | not null
Indexes:
"requests_pkey" PRIMARY KEY, btree (id)
Check constraints:
"allowed_status_types" CHECK (status::text = ANY (ARRAY['pending'::character varying, 'success'::character varying, 'failure'::character varying]::text[]))
但是是否有可能编写一个专门返回allowed_status_types未决，成功，失败的查询？
能够在我的应用程序中记住此查询的结果，而不需要维护重复的副本，将是很棒的。
您可以查询系统目录pg_constraint，例如：
select consrc
from pg_constraint
where conrelid = 'requests'::regclass
and consrc like '(status%';
consrc
---------------------------------------------------------------------------
(status = ANY (ARRAY['pending'::text, 'success'::text, 'failure'::text]))
(1 row)
使用以下函数解压缩字符串：
create or replace function get_check_values(str text)
returns setof text language plpgsql as $$
begin
return query
execute format (
'select * from unnest(%s)',
regexp_replace(str, '.*(ARRAY\[.*\]).*', '\1'));
end $$;
select get_check_values(consrc)
from pg_constraint
where conrelid = 'requests'::regclass
and consrc like '(status%';
get_check_values
------------------
pending
success
failure
(3 rows)
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句