Trying to create array of enum to array of text for exclusion constraint

Поиск
Список
Период
Сортировка
От Steven Lembark
Тема Trying to create array of enum to array of text for exclusion constraint
Дата
Msg-id 20160505202404.40798414@cannibal
обсуждение исходный текст
Ответы Re: Trying to create array of enum to array of text for exclusion constraint  (Emre Hasegeli <emre@hasegeli.com>)
Список pgsql-general
Using Pg 9.5.2 on linux.

Trying to create an exclusion constraint on an array of enums.
Ultimate goal is having a constraint that excludes records with
overlapping elements.

This must have been done before, I just cannot find any examples.

I realize there isn't a q&d way to convert enums to integers
(e.g., <http://stackoverflow.com/questions/12344213/postgresql-is-it-possible-cast-enum-to-integer#12347716>)
but there should be a way to convert enums to text for this purpose.

For example, with a scalar enum this works:

e.g.,

    drop type if exists week_day cascade;
    create type week_day as
    enum
    (
        'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun'
    );

    /*
     * works for exclude using gist as "week_day_text( X ) with =".
     */
    create or replace function week_day_text
    (
      week_day
    )
    returns text
    language sql strict immutable as
    $$
      select $1::text;
    $$
    ;

    /*
     * this works if days is week_day w/o array and
     * the exclusion uses week_day_text( day ).
     */

    drop table if exists timeslot cascade;
    create table timeslot
    (
        /*
         * this would normally also have hours,
         * for this example weekday is sufficient.
         */

        day week_day   not null,

        exclude using gist
        (
            week_day_text( day ) with =
        )
    );


Goal is replacing day with an array of week_day as:

    day week_day[] not null,

Using "day with &&" leaves me with (whitespace added):

    drop table if exists timeslot cascade;
    create table timeslot
    (
        /*
         * this would normally also have hours,
         * for this example weekday is sufficient.
         */

        day week_day[]  not null,   /* add array of enum */

        exclude using gist
        (
            day with &&
        )
    );

psql:hak:43: ERROR:  data type week_day[] has no
default operator class for access method "gist"
HINT:  You must specify an operator class for
the index or define a default operator class for
the data type.

Using the text function blows up because it doesn't support arrays
(again, whitespace added for readability):

    (
        ...

        exclude using gist
        (
            week_day_text( day ) with &&
        )
    );


psql:hak:43: ERROR:  function week_day_text(week_day[]) does not exist
LINE 10:             week_day_text( day ) with &&
                     ^
HINT:  No function matches the given name and
argument types. You might need to add explicit type casts.


Using array_to_string won't be sufficient since that would allow
overlaps due to different orders of array elements.

So... what I think I need is a plsql function that takes an
array of weekday and retuns an array of text?

    /*
     * convert array of week_day enum values to array of
     * text for exclusion constraints.
     */

    create or replace function week_day_array_text
    (
      week_day[]
    )
    returns text[]
    language sql strict immutable as
    $$
        /*
         * what is the syntax for generating this array?
         * effectively I need a "map { $1::text }" in plsql.
         */
    $$
    ;

or is there something built in that I have missed?

Note: Performance will not be an issue here as the table is not
updated all that frequently.

Any references appreciated.

--
Steven Lembark                                           3646 Flora Pl
Workhorse Computing                                 St Louis, MO 63110
lembark@wrkhors.com                                    +1 888 359 3508


В списке pgsql-general по дате отправления:

Предыдущее
От: Eric Ridge
Дата:
Сообщение: Re: How to manually force a transaction wraparound
Следующее
От: rob stone
Дата:
Сообщение: Re: Debian and Postgres