From: | Gary Chambers <gwchamb(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Data Model Advice |
Date: | 2010-09-15 16:04:30 |
Message-ID: | AANLkTin49WyBk7w-eANFL4dfyZ=2_D+LooFHX=x+9K=z@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
All,
My data modeling and SQL are oftentimes woefully inadequate, and I am
seeking advice on how to implement a particular database design. My
database (so far) is comprised of the following types of tables:
The parts table contains parts of a particular type primary key is an
alphanumeric part number, some with dashes.
Table "public.parts"
Column | Type | Modifiers
------------+-----------------------+---------------------------------
partno | character varying(64) | not null
partno_raw | character varying(64) | default NULL::character varying
boxno | integer | not null
slotno | integer | not null
Indexes:
"pk_parts" PRIMARY KEY, btree (partno)
The parts_subs table contains part numbers different manufacturers
that are identical to something that already exists in the parts table
Table "public.parts_subs"
Column | Type | Modifiers
-------------+-----------------------+--------------------
partno | character varying(64) | not null
partsub | character varying(64) | not null
partsub_raw | character varying(64) | not null
boxno | integer | not null
slotno | integer | not null
Indexes:
"pk_parts_subs" PRIMARY KEY, btree (partno, partsub)
I'm trying to determine, and I'm seeking advice on:
How to maintain the original part number format but create the
primary key with only alphanumeric. I currently have a 'before insert
or update' trigger to strip the non-alphanumeric characters, but it
doesn't permit the insertion of a record because it is null on insert
(for some reason). The trigger function, which I'm trying to make as
flexible as possible, is:
CREATE OR REPLACE FUNCTION fixup_partnumbers() RETURNS TRIGGER AS
$fixup_partnumbers$
BEGIN
IF (STRPOS(TG_TABLE_NAME, '_subs') > 0) THEN
NEW.partsub := REGEXP_REPLACE(NEW.partsub_raw, E'(\\W|_)', '', 'g');
ELSE
NEW.partno := REGEXP_REPLACE(NEW.partno_raw, E'(\\W|_)', '', 'g');
END IF;
RETURN NEW;
END;
$fixup_partnumbers$ LANGUAGE plpgsql;
Thank you in advance for any advice and assistance you can provide.
-- Gary Chambers
From | Date | Subject | |
---|---|---|---|
Next Message | Carlos Mennens | 2010-09-15 16:05:25 | Re: Table Comments |
Previous Message | Steve Crawford | 2010-09-15 16:03:04 | Re: Table Comments |