创建自定义域Postgres数组 [英] Create array of custom domain postgres
问题描述
由于枚举的继承限制(您不能从函数内部向枚举添加值),因此我将使用检查约束来验证值,以切换到自定义域。我需要能够创建自定义枚举的数组,但是当我尝试这样的操作时:
Because of the inherit limitations of enum (you can't add values to the enum from within a function), I'm switching to custom domains with a check constraint verifying the values. I need to be able to create arrays of my custom enums, but when I try something like this:
CREATE DOMAIN foo AS text CHECK (VALUE IN ('foo', 'bar'));
CREATE TABLE foo_table(foo_column foo[]);
我遇到错误
type "foo[]" does not exist
做点谷歌搜索,我发现这是2004年起的,这看起来像是对此的支持未来。
Doing some googling, I found this from 2004 which made it look like support for this was coming. Is there a way to do this?
谢谢!
我想出了一个hacky解决方案,如果几天之内没有人提出更好的解决方案,我将以此为答案。此解决方案意味着您不能重复使用某个类型作为数组,而必须创建一个充当数组的单独类型:
I've come up with a hacky solution, which I'll put as the answer if no one comes up with a better solution in a few days. This solution means you can't reuse a type to be an array, you have to create a separate type that acts as the array:
CREATE DOMAIN foo_group AS text[] CHECK (VALUE <@ ARRAY['foo', 'bar']);
CREATE TABLE foo_table(foo_column foo_group);
以下工作:
INSERT INTO foo_table VALUES(ARRAY['foo']);
INSERT INTO foo_table VALUES(ARRAY['foo', 'bar']);
INSERT INTO foo_table VALUES(ARRAY['bar']);
以下内容不:
INSERT INTO foo_table VALUES(ARRAY['foo', 'baz']);
INSERT INTO foo_table VALUES(ARRAY['baz']);
推荐答案
另一个可能的解决方法是:
Another possible workaround is:
CREATE TYPE foo_tup AS (item foo);
域类型可以包装在这样的元组中,从而为您提供数组构造函数。缺点是您现在可能想创建演员表:
Domain types can wrapped in tuples like this and that gives you an array constructor. The downside is now you probably want to create casts:
select array[row('foo')::foo_tup, row('bar')];
例如,您可以创建一个函数和强制类型转换:
For example you could create a function and a cast:
create function foo_tup(foo) returns foo_tup language sql as $$
select row($1)::foo_tup;
$$ immutable;
create function foo(foo_tup) returns foo language sql as $$
select $1.item;
$$;
create cast (foo as foo_tup) with function foo_tup(foo);
create cast (foo_tup as foo) with function foo(foo_tup);
然后聚合变得容易:
select array_agg(myfoo::foo_tup) from my_table;
尽管您会得到额外的括号。
though you get extra parentheses.
这篇关于创建自定义域Postgres数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!