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

[Steven Lembark <lembark@xxxxxxxxxxx>]

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@xxxxxxxxxxx                                    +1 888 359 3508


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general