On Wed, Mar 9, 2011 at 10:59 AM, Reece Hart <reece@xxxxxxxxx> wrote: > On Tue, Mar 8, 2011 at 9:41 PM, John R Pierce <pierce@xxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
