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
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 |