List all Postgres enum array values combined with values applied on the record showing a status if applied or not

Recently I've rediscovered Postgres user defined types and arrays which made me feel it Postgres has been NoSQL (not only SQL) far before the term became popular.

There is a need to make a selection of an array of enum values column to show all enum values and a flag if the enum array column included enum value or not having the right sorting (firstly sort by column applied enum values and then by enum values order if enum value has not been included into a column)

An example DDL is:

-- 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;

There could be a huge amount of website records, there will be mostly reads and settings enum will be expanded in the future, so having the settings 'included' into the website record looks much better solution than using many-to-many table.

I've started with UDF (just for example, it won't work like expected) like:

  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;

which I didn't manage to make work like expected.

How would you get the right menu_extras_with_defaults value in this case?

Solution

unnest(...) WITH ORDINALITY is your friend here:

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)$$;

Let me add a word or two of advice here.

Even though PostgreSQL supports composite data types and arrays, you should't start using them everywhere.

There is no need to use arrays and composite types here. website may as well have just the id, and menu_extras should be a second table referencing website.

Moreover, don't use enum types unless you are certain that the range of values will never change. For example, you cannot remove a value from an enum type. It is better to use a regular lookup table.

I predict that it would be much easier to write the function you want if you change the data model like that.

Non-atomic data types can be a powerful tool in the rare cases where you need them, but they can cause a lot of pain if used indiscriminately.