From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Reece Hart <reece(at)harts(dot)net> |
Cc: | John R Pierce <pierce(at)hogranch(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: equivalent of mysql's SET type? |
Date: | 2011-03-09 17:16:57 |
Message-ID: | AANLkTinMTUsfCj4T8jNi3Nkw+s5v1ocTe59Fhcmv2XxT@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Mar 9, 2011 at 10:59 AM, Reece Hart <reece(at)harts(dot)net> wrote:
> On Tue, Mar 8, 2011 at 9:41 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:
>>
>> why not just have a set of booleans in the table for these individual
>> on/off attributes? wouldn't that be simplest?
>
> I like that approach, but I think it's unlikely to fly in this specific case
> for a couple reasons.
> First, there are actually 8 factors (I edited for clarity... sorry about
> that).
> The original database is actively developed (released apx quarterly). I will
> need an approach that minimizes my burden when they edit the set factors.
> And, I'd like to be compatible with mysql syntax and semantics for sets. If
> you hold your nose for a moment, you'll be able to read the following
> without becoming ill: mysql uses comma delimited strings to assign and query
> set types (but stored internally as bit vectors). So, one does
> validation_status = 'cluster,freq' to set those bits or validation_status
> like '%freq%' to query. Much to my chagrin, emulating this interface will
> make migration easier. However, implementing this string interface to
> set/get boolean columns is just too offensive to whatever modest design
> sensibilities I have. (For more pleasure reading, see
> http://dev.mysql.com/doc/refman/5.0/en/set.html. I particularly like the
> *warning* issued when one tries to add a value that's not part of the set.)
> -Reece
create type validation_flags as
(
cluster bool,
freq bool
);
create function validation_flags_in(
flags text, flags out validation_flags) returns validation_flags as
$$
select row($1 ~ 'cluster', $1 ~ 'freq')::validation_flags
$$ language sql immutable;
create table foo (flags validation_flags);
insert into foo values (validation_flags_in('cluster'));
select * from foo;
select (flags).* from foo;
merlin
From | Date | Subject | |
---|---|---|---|
Next Message | runner | 2011-03-09 17:28:19 | Mounting file system for WAL on Solaris 10 |
Previous Message | Reece Hart | 2011-03-09 16:59:14 | Re: equivalent of mysql's SET type? |