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

Поиск
Список
Период
Сортировка
Искать

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

От:
Steven Lembark <lembark@wrkhors.com>
Дата:

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., )
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

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

От:
Emre Hasegeli <emre@hasegeli.com>
Дата:
> or is there something built in that I have missed?

The intarray extension in the contrib provides a GiST operator class
for int[].  That can be used with exclusion constraints:

> hasegeli=# create extension intarray;
> CREATE EXTENSION
>
> hasegeli=# create type e as enum ('a', 'b');
> CREATE TYPE
>
> hasegeli=# create table t (es e[]);
> CREATE TABLE
>
> hasegeli=# create function es_to_int (e[]) returns int[] language sql immutable as $$
>             select array_agg(oid::int) from pg_enum
>             where enumtypid = (select oid from pg_type where typname = 'e')
>                 and enumlabel = any($1::text[])$$;
> CREATE FUNCTION
>
> hasegeli=# alter table t add exclude using gist (es_to_int(es) with &&);
> ALTER TABLE
>
> hasegeli=# insert into t values ('{a,b}');
> INSERT 0 1
>
> hasegeli=# insert into t values ('{a}');
> ERROR:  conflicting key value violates exclusion constraint "t_es_to_int_excl"
> DETAIL:  Key (es_to_int(es))=({114830}) conflicts with existing key (es_to_int(es))=({114830,114832}).
FAQ