本文介绍了PostgreSQL查询约束中允许值的列表?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
给出一个名为 requests 的PostgreSQL表,该表具有一个名为 status 的列和一个约束,如下所示:
Given a PostgreSQL table named requests with a column named status and a constraint like this:
ALTER TABLE requests ADD CONSTRAINT allowed_status_types CHECK (status IN ( 'pending', -- request has not been attempted 'success', -- request succeeded 'failure' -- request failed ));在 psql 中,我可以像这样拉出有关此约束的信息:
In psql I can pull up information about this constraint like this:
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 的查询吗?
But is it possible to write a query that specifically returns the allowed_status_types of pending, success, failure?
能够在我的应用程序中记住此查询的结果,而不需要维护重复的副本,真是太好了.
It would be great to be able to memoize the results of this query within my application, vs. having to maintain a duplicate copy.
推荐答案您可以查询系统目录 pg_constraint ,例如:
You can query the system catalog pg_constraint, e.g.:
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)更多推荐
PostgreSQL查询约束中允许值的列表?
发布评论