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

Question

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?

Laurenz Albe · Accepted Answer · 2019-02-17 19:10:26Z

2

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.

answered Feb 17, 2019 at 19:10

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zelid Over a year ago

I haven't used Postgres for a long time in production, and there is a big doubt about composite types exactly with points mentioned by you Laurenz, but if composite type works better for read performance over join table I would choose it. Just because of a read performance. Mostly there will be a check if website.menu_extras includes value or not inside the website and website_menu_extras_with_defaults is needed for configurator UI, when it's easier to override a composite type array then do an

Zelid Over a year ago

upsert on m:m website_manu_extras_map(website_id, menu_extra_id, is_enabled) table (remove all records where website_id = ...) and insert new records. And also no need to join with menu_extra table to get the all available values which are not listed inside website_manu_extras_map which would be needed for configurator UI. Again, I'm not sure yet before doing performance tests.

Laurenz Albe Over a year ago

This was just my opinion. Maybe it will work out fine for you.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related