Search Postgresql Archives

Data Model Advice

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux