Re: EVAL and SET equivalents in PostgreSQL

On Wed, 3 Jul 2002, Lynn David Newton wrote:

> No one who has responded (four people so far) has yet
> quite hit the nail on the head regarding a substitute
> for MySQL's SET data type.

Well, as someone else pointed out, there's always the option of
doing it the relational way, and just creating a separate table
for this.

However, if you want to do what MySQL does internally, and do it
in a more portable way, you can just use bitfields in an int or
decimal of appropriate size. Just assign numbers to your enum values
(0 = foo, 1 = bar, 2 = bam., etc.) and use bit operations to set
and unset them.

    -- Set foo:
    UPDATE mytable SET flags = flags & (1 << 0) WHERE ...
    -- Set bam:
    UPDATE mytable SET flags = flags & (1 << 2) WHERE ...
    -- Unset bar:
    UPDATE mytable SET flags = flags | ~(1 << 1) WHERE ...

You may find it convenient to create functions to deal with this,
so you can just pass in 'foo' or 'bam' rather than having to convert
to the number.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC