Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • by djberg96 (2603) on 2006.04.21 9:32 (#47306) Journal
    Oh, good point. Is something like PostgreSQL's array type the proper solution here then?

    create table sometable (
       id serial,
       name varchar(50),
       email text[]
    );

    This would store a list of email addresses as a single object. Or have I missed the point again?

    In other news, I'm surprised PostgreSQL doesn't have an "email_address" type. It seems to have everything else.

    • In other news, I'm surprised PostgreSQL doesn't have an "email_address" type. It seems to have everything else.

      You can create one easily enough with a CREATE DOMAIN statement. Here's a naive implementation:

      CREATE DOMAIN email_address AS TEXT
      CONSTRAINT ck_email_address CHECK (
         VALUE ~ '^[^@]+@[^@]+$'
      );

      Modify the regular expression as needed to increase its accuracy. Then you can use it like any other PostgreSQL data type:

      CREATE TABLE person (
        id SERIAL PRIMARY KEY,
        name text

    • As Theory points out, you can create such a type. The problem is that if anything else needs to reference data within that type, Postgres does not allow any convenient way of doing so. This makes it pretty difficult to use Postgres for MV purposes.

      • PostgreSQL actually supports composite types.

        CREATE TYPE inventory_item AS (
            name            text,
            supplier_id     integer,
            price           numeric
        );

        CREATE TABLE on_hand (
            item      inventory_item,
            count     integer
        );

        SELECT (item).supplier_id FROM on_hand;

        It sounds like PostgreSQL even automatically makes a composite type for each table

    • How do you then update, insert, delete, or search for a single email address? If it makes things more complicated to deal with the data, I think I'd rather normalize. Hmm, looking at the postgres docs, searching for a value in an array:

      SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                                  pay_by_quarter[2] = 10000 OR
                               

      • The problem with CRUD on a single email address goes away if you have a language which supports that. SQL does not. While I confess that I don't remember my UniQuery (the query language for the MV database I used), imagine something like this:

        DELETE email
        FROM   customer
        WHERE  name  = ?
          AND  email = ?

        In other words, DELETE would operate on more than just tuples.

        How about searching for a particular email? Well, you show some sample syntax above.

        There are plenty of oth