能否在目标表中通过静态表达式引用字典表条目？

我有一个dict表，希望通过字典名称来引用其中的条目，请问这个需求是否可行？我预想的SQL语句示例如下：

-- 'SourceType' 用于限定引用范围
foreign key (tid, 'SourceType', source_type) references dict_entry (tid, dict_code, value)

目前我是通过创建大量独立字典表来实现类似功能的，比如：

create table if not exists source_type
(
    tid           text  not null default public.current_tenant_id() references public.tenant (tid),
    value         text,
    label         text,
    display_order bigint         default nextval('seq_display_order'),
    attributes    jsonb not null default '{}'::jsonb,
    properties    jsonb not null default '{}'::jsonb,
    extensions    jsonb not null default '{}'::jsonb,
    primary key (tid, value)
);

我希望用单个dict表替代大量上述这类独立字典表。

现有实现代码 #

create table if not exists dict
(
    id           text        not null default 'dict_' || public.gen_ulid() primary key,
    uid          uuid        not null default gen_random_uuid() unique,
    created_at   timestamptz not null default current_timestamp,
    updated_at   timestamptz not null default current_timestamp,
    deleted_at   timestamptz,
    tid          text        not null default public.current_tenant_id() references public.tenant (tid),
    eid          text,

    display_name text        not null,
    description  text,
    code         text        not null default 'D' || to_char(now(), 'YYYYMMDD') || (regexp_match(gen_random_uuid()::text, '([0-9a-f]{6})'))[1],
    value_schema jsonb                default '{
      "type": "string"
    }',
    sequence     bigint      not null default 0,
    system       bool        not null default false,

    attributes   jsonb       not null default '{}',
    properties   jsonb       not null default '{}',
    extensions   jsonb       not null default '{}',
    unique (tid, display_name),
    unique (tid, code)
);

create or replace function er.next_dict_value(in_dict_code text, in_tid tenant.tid%TYPE = current_tenant_id())
    returns jsonb
    language plpgsql
    volatile
as
$$
declare
    out_next dict.sequence%TYPE;
    _schema  dict.value_schema%TYPE;
begin
    if in_dict_code is null then
        raise exception 'Empty sequence'
            using hint = 'check you table definition';
    end if;
    select value_schema into _schema from dict where tid = in_tid and code = in_dict_code;

    -- schema是字符串类型时使用uuid
    if _schema ->> 'type' = 'string' then
        return to_jsonb(('V' || public.gen_ulid()));
    end if;

    -- schema是整数类型时使用自增序列
    if _schema ->> 'type' = 'integer' then
        update dict
        set sequence=sequence + 1,
            updated_at = now()
        where tid = in_tid
          and code = in_dict_code
        returning sequence into out_next;
        if out_next is null
        then
            raise exception 'Empty sequence'
                using hint = 'ensure you dict exists: ' || in_dict_code;
        end if;
        return to_jsonb(out_next);
    end if;
    raise exception 'Empty sequence'
        using hint = 'unknown schema type: ' || in_dict_code;
end;
$$;

create table if not exists dict_entry
(
    id            text        not null default 'dicte_' || public.gen_ulid() primary key,
    uid           uuid        not null default gen_random_uuid() unique,
    created_at    timestamptz not null default current_timestamp,
    updated_at    timestamptz not null default current_timestamp,
    deleted_at    timestamptz,
    tid           text        not null default public.current_tenant_id() references public.tenant (tid),
    eid           text,

    dict_code     text        not null,
    label         text        not null,
    value         jsonb       not null,
    description   text,
    display_order int         not null default nextval('seq_display_order'),
    active        bool        not null default true,
    metadata      jsonb       not null default '{}',

    attributes    jsonb       not null default '{}',
    properties    jsonb       not null default '{}',
    extensions    jsonb       not null default '{}',
    unique (tid, eid),
    unique (tid, dict_code, value),
    foreign key (tid, dict_code) references dict (tid, code) on update cascade on delete cascade
);

待替代的多表实现示例 #

create table if not exists gender_type
(
    like tpl_dict_type including all
);

insert into gender_type (value, label)
values ('Male', '男'),
       ('Female', '女')
on conflict(tid,value) do update set (label, extensions) = (excluded.label, excluded.extensions);

内容的提问来源于stack exchange，提问作者wener