时间戳

首页 > 解决方案 > 列出所有 Postgres 枚举数组值以及应用在记录上的值，如果应用或不应用则显示状态

arrays - 列出所有 Postgres 枚举数组值以及应用在记录上的值，如果应用或不应用则显示状态

问题描述

最近我重新发现了 Postgres 用户定义的类型和数组，这让我觉得 Postgres 在这个术语流行之前就已经是 NoSQL（不仅仅是 SQL）了。

如果枚举数组列包含枚举值或没有正确排序，则需要选择枚举值列的数组以显示所有枚举值和一个标志（首先按列应用的枚举值排序，然后按枚举值排序如果枚举值未包含在列中，则排序）

一个示例 DDL 是：

-- available website settings enum:
CREATE TYPE website_menu_extras_type AS ENUM ( 'logo', 'emails', 'locations', 'phones', 'search' );

-- this type represents if website setting is enabled or not (the setting is treated as enabled if it's included to a website's column `menu_extras` of type `website_menu_extras_type[]`):
CREATE TYPE website_menu_extras_with_defaults AS ( menu_extra website_menu_extras_type, is_enabled BOOLEAN );

-- website table which contains applied `website_menu_extras_type` values as array:
CREATE TABLE website ( ID serial PRIMARY KEY, menu_extras website_menu_extras_type [] );

-- insert some values:
INSERT INTO website ( menu_extras )
VALUES
    ( ARRAY [ 'logo', 'emails' ]:: website_menu_extras_type [] );

-- select `menu_extras` as applied values 
-- and `menu_extras_with_defaults` which is an array 
-- of website_menu_extras_with_defaults having 
-- all values of `website_menu_extras_type` enum
-- and `is_enabled` set to true if `menu_extras` includes enum value
-- and `is_enabled` set to false if `menu_extras` does not include enum value
-- `menu_extras_with_defaults` should be sorted by `menu_extras` order 
-- and then by `website_menu_extras_type` enum order if `menu_extras` didn't include the enum value
SELECT
    id, menu_extras, menu_extras as menu_extras_with_defaults
FROM
    website;

可能会有大量的网站记录，未来将主要是读取和设置枚举，因此将设置“包含”到网站记录中看起来比使用多对多表更好的解决方案。

我从 UDF 开始（例如，它不会像预期的那样工作），例如：

  CREATE FUNCTION website_menu_extras_with_defaults ( website website ) RETURNS website_menu_extras_with_defaults[] AS $$ 
  WITH
    all_enum_values
    AS
    (
      SELECT UNNEST
        (
        enum_range ( NULL
     :: website_menu_extras_type )) AS val 
    ),
    all_enum_values1 AS
  (
    SELECT UNNEST
        (
        enum_range ( NULL
  :: website_menu_extras_type )) AS val 
    )
  -- select * from x1
  SELECT

    array[( val, FALSE ), (val, TRUE)]
  :: website_menu_extras_with_defaults []
FROM
    all_enum_values
-- where val in (website).menu_extras
    $$ LANGUAGE SQL STABLE;

我没能像预期的那样工作。

在这种情况下，您将如何获得正确的menu_extras_with_defaults价值？

标签： arrayspostgresqlenumscomposite-types

解决方案

unnest(...) WITH ORDINALITY你的朋友在这里吗：

CREATE OR REPLACE FUNCTION website_menu_extras_with_defaults(website website)
   RETURNS website_menu_extras_with_defaults[]
   LANGUAGE sql AS
$$SELECT array_agg(
            ROW(et.e, me.num IS NOT NULL)::website_menu_extras_with_defaults
               ORDER BY me.num, et.num
         )
FROM unnest(enum_range(NULL::website_menu_extras_type)) WITH ORDINALITY AS et(e, num)
   LEFT JOIN unnest(website.menu_extras) WITH ORDINALITY AS me(e, num)
      USING (e)$$;

让我在这里补充一两句话。

即使 PostgreSQL 支持复合数据类型和数组，您也不应该在任何地方开始使用它们。

这里不需要使用数组和复合类型。website还不如刚才的id，menu_extras应该是第二个表引用website。

enum此外，除非您确定值的范围永远不会改变，否则不要使用类型。例如，您不能从enum类型中删除值。最好使用常规查找表。

我预测如果你像这样改变数据模型，编写你想要的函数会容易得多。

非原子数据类型在您需要它们的极少数情况下可能是一个强大的工具，但如果不加选择地使用它们会导致很多痛苦。

推荐阅读