PostgreSQL查询约束中允许值的列表? [英] PostgreSQL query for a list of allowed values in a constraint?

问题描述

给出一个名为 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.

推荐答案

您可以查询系统目录

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查询约束中允许值的列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！