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

From: Steven Lembark <lembark(at)wrkhors(dot)com>
To: pgsql-general(at)postgresql(dot)org
Cc: lembark(at)wrkhors(dot)com
Subject: Trying to create array of enum to array of text for exclusion constraint
Date: 2016-05-06 01:24:04
Message-ID: 20160505202404.40798414@cannibal
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)wrkhors(dot)com +1 888 359 3508

Responses

Browse pgsql-general by date

  From Date Subject
Next Message rob stone 2016-05-06 02:29:13 Re: Debian and Postgres
Previous Message Eric Ridge 2016-05-06 01:04:03 Re: How to manually force a transaction wraparound