On Wed, Sep 15, 2010 at 12:04 PM, Gary Chambers <gwchamb@xxxxxxxxx> 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); -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
