Re: equivalent of mysql's SET type?

On Thu, Mar 10, 2011 at 3:21 PM, Reece Hart <reece(at)harts(dot)net> wrote:
> On Wed, Mar 9, 2011 at 9:16 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>>
>> create type validation_flags as
>> (
>>  cluster bool,
>>  freq bool
>> );
>
> Wow. That solution is nearly sexy, and far and away better than any solution
> that I would have come up with. Thanks, Merlin!

thanks -- I do what I do. fyi another thing is this only works if no
flags substrings of other flag -- no big deal to add a little guard
against that in the 'in' function though if you need to.
also one pain point with composite types is that you can't flip
specific fields like this:

update foo set (flags).freq = true;

you can work around that like this:

create function validation_flags_out(
flags validation_flags, flags out text) returns text as
$$
select array_to_string(array(
select 'freq' where ($1).freq
union all
select 'cluster' where ($1).cluster
), ',')
$$ language sql immutable;

set a flag:
update foo set flags = validation_flags_in(validation_flags_out(flags)
|| ',cluster');

remove a flag:
update foo set flags =
validation_flags_in(replace(validation_flags_out(flags), 'cluster',
''));

if you do the above a lot you can wrap the function so that your code becomes:
update foo set flags = validation_flags(flags, 'cluster', true/false);

it really depends on how much process/structure you want to wrap
around this to get it to your liking. another completely separate
route to this problem is the hstore type:
http://www.postgresql.org/docs/9.0/static/hstore.html

which is a very good syntax generalization of key/value pairs, but it
maybe too loose if you want to reduce to specific flag set, but you
should probably take a look before writing a bunch of functions. If
you do go with flags/composite type route, and you had a lot of mysql
'set' types to convert, I would write a function in postgres to
generate the wrapper functions from list of strings -- ping back if
you need help with that.

merlin