From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Gary Chambers <gwchamb(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Data Model Advice |
Date: | 2010-09-15 19:55:26 |
Message-ID: | AANLkTin-Gov0ZvFy-wbLb03SOZq51H8ajgxhzFkNB_A7@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Sep 15, 2010 at 12:04 PM, Gary Chambers <gwchamb(at)gmail(dot)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);
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Hopfgartner | 2010-09-15 20:11:26 | Re: Getting FATAL: terminating connection due to administrator command |
Previous Message | Gary Chambers | 2010-09-15 19:42:46 | Data Model Advice |