Data Model Advice

On Wed, Sep 15, 2010 at 12:04 PM, Gary Chambers <gwchamb@gmail.com> wrote:

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.

hm. let me make a general rule of thumb suggestion: generic
behaviors, generic trigger function, specific behaviors, specific
trigger function. setting a field that is only on a particular table
is specific and I'd prefer to make two trigger functions, which both
call something like:

create or replace function fixup_partno(text) returns text as
$$
SELECT REGEXP_REPLACE(NEW.partno_raw, E'(\\W|_)', '', 'g');
$$ language sql immutable;

You should in theory be able to write a before trigger to intercept
and modify any field before it hits the table, including the primary
key. The following works for me:
create table foo(t text primary key);

create or replace function fixup_foo() returns trigger as
$$
begin
new.t := random()::text;
return new;
end;
$$ language plpgsql;

create trigger on_foo_insert before insert on foo for each row
execute procedure fixup_foo();

insert into foo values (null);
insert into foo values (null);
insert into foo values (null);