PostgreSQL: trigger to autopopulate other fields with sub-strings of other field inserted

Question

I am looking to insert 1 to 8 sub-string values from an inserted string value into other fields as integers using a trigger in PostgreSQL. The integer values have a string prefix to identify them within the string value. A specific solution using my details below or a generalized example that helps me see how it can be done would be great. I want to do it this way (getting the integers from the 'calc_form' field string value) for control as mentioned in the "Discarding 3NF With Triggers" section of this link: http://database-programmer.blogspot.com/2008/01/database-skills-third-normal-form-and.html

The extent of my knowledge on triggers can be seen here (I am new to it): http://www.w3resource.com/PostgreSQL/postgresql-triggers.php

Here are the real life details, which may not be necessary to some. A solution that does everything I need below is not necessary.

The string values are identified/prefixed with bp or wc before the integer I want to insert with the trigger. Where the bp values go in the bbg_pulls_[#]_id fields and the wc go in 'wh_calc_[#]_id` fields.

Everything happens within this table:

CREATE TABLE wh_calc
(
  id serial NOT NULL,
  calc_form character varying(500) NOT NULL,
  bbg_pulls_1_id integer,
  bbg_pulls_2_id integer,
  bbg_pulls_3_id integer,
  bbg_pulls_4_id integer,
  bbg_pulls_5_id integer,
  bbg_pulls_6_id integer,
  bbg_pulls_7_id integer,
  bbg_pulls_8_id integer,
  wh_calc_1_id integer,
  wh_calc_2_id integer,
  wh_calc_3_id integer,
  wh_calc_4_id integer,
  wh_calc_5_id integer,
  wh_calc_6_id integer,
  wh_calc_7_id integer,
  wh_calc_8_id integer,
  CONSTRAINT wh_calc_pkey PRIMARY KEY (id),
  CONSTRAINT wh_calc_calc_form_key UNIQUE (calc_form)
)

For example, this insert:

insert into wh_calc
(calc_form)
values
('[bp20]/[bp47140]');

...should yield the same result as this insert:

insert into wh_calc
(calc_form,bbg_pulls_1_id,bbg_pulls_2_id)
values
('[bp20]/[bp47140]',20,47140);

Here is another, slightly more complicated example. This insert:

insert into wh_calc
(calc_form,bbg_pulls_1_id,wh_calc_1_id,wh_calc_2_id)
values
('([bp1]/[wc66])*[wc100]');

...should yield the same result as:

insert into wh_calc
(calc_form,bbg_pulls_1_id,wh_calc_1_id,wh_calc_2_id)
values
('([bp1]/[wc66])*[wc100]',1,66,100);

Note, that in both examples I want to insert without the integer values and have the integers show up like the inserts with the integers. The inserts with the integers are only there to illustrate the result I want.

Answer 1

The required trigger is quite fancy. This is the trigger function:

CREATE FUNCTION wh_calc_ins_upd_trg_func()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    t text[] ;
BEGIN    
    SELECT array_agg(x[1]) 
    FROM regexp_matches(new.calc_form, '\[bp(\d+)\]', 'gi') AS x 
    INTO t ;

    new.bbg_pulls_1_id := t[1]::integer ;
    new.bbg_pulls_2_id := t[2]::integer ;
    new.bbg_pulls_3_id := t[3]::integer ;
    new.bbg_pulls_4_id := t[4]::integer ;
    new.bbg_pulls_5_id := t[5]::integer ;
    new.bbg_pulls_6_id := t[6]::integer ;
    new.bbg_pulls_7_id := t[7]::integer ;
    new.bbg_pulls_8_id := t[8]::integer ;

    SELECT array_agg(x[1]) 
    FROM regexp_matches(new.calc_form, '\[wc(\d+)\]', 'gi') AS x 
    INTO t ;

    new.wh_calc_1_id := t[1]::integer ;
    new.wh_calc_2_id := t[2]::integer ;
    new.wh_calc_3_id := t[3]::integer ;
    new.wh_calc_4_id := t[4]::integer ;
    new.wh_calc_5_id := t[5]::integer ;
    new.wh_calc_6_id := t[6]::integer ;
    new.wh_calc_7_id := t[7]::integer ;
    new.wh_calc_8_id := t[8]::integer ;

    RETURN new ;
END ;
$BODY$ ;

And this is the association of this function to a trigger that should be fired before insert or update of column 'calc_form'. [or...]

CREATE TRIGGER wh_calc_ins_upd_trg
    BEFORE INSERT OR UPDATE OF calc_form
    ON wh_calc
    FOR EACH ROW
    EXECUTE PROCEDURE wh_calc_ins_upd_trg_func();

This trigger plays with a few concepts:

1. Regular Expressions to capture the integers in [bp___] and [wc___]. The regexp_matches function returns a SET of text[].
2. The SET of text[] is converted into an array of arrays by means of array_agg.
3. We convert from text to integer, by means of tx::integer.

You can try then:

INSERT INTO 
    wh_calc
    (calc_form)
VALUES
    ('[bp20]/[bp3]') 
RETURNING 
    * ;

And see that you get what you expect:

| id |    calc_form | bbg_pulls_1_id | bbg_pulls_2_id | bbg_pulls_3_id | bbg_pulls_4_id | bbg_pulls_5_id | bbg_pulls_6_id | bbg_pulls_7_id | bbg_pulls_8_id | wh_calc_1_id | wh_calc_2_id | wh_calc_3_id | wh_calc_4_id | wh_calc_5_id | wh_calc_6_id | wh_calc_7_id | wh_calc_8_id |
|----|--------------|----------------|----------------|----------------|----------------|----------------|----------------|----------------|----------------|--------------|--------------|--------------|--------------|--------------|--------------|--------------|--------------|
|  4 | [bp20]/[bp3] |             20 |              3 |         (null) |         (null) |         (null) |         (null) |         (null) |         (null) |       (null) |       (null) |       (null) |       (null) |       (null) |       (null) |       (null) |       (null) |

You can check it a SQLFiddle

Answer 2

It seems as if these ranges are ordinal (position matters). And that capping them at 8 or whatever is arbitrary. Rather than storing 8 potential variables per series for two discrete series, why don't you store two arrays on the table.

bbg_pulls int[];
wh_calc int[];

You can still create indexes over these types. You'll just have an easier time assembling them and querying them.

For instance,

WHERE bbg_pulls @> ARRAY[66]

Is a ton easier than

WHERE (
     bbg_pulls_1 = 66
  OR bbg_pulls_2 = 66
  OR bbg_pulls_3 = 66
  OR bbg_pulls_4 = 66
  OR bbg_pulls_5 = 66
  OR bbg_pulls_6 = 66
  OR bbg_pulls_7 = 66
  OR bbg_pulls_8 = 66
);

It's also far easier to control uniqueness as a COLUMN check constraint. Than it is to handle a multi-column check constraint (using intarray). If your workload could use that.

Schema, Trigger, and test.

Here is how this would look. First we create the table with DDL.

CREATE TEMP TABLE wh_calc (
  id serial NOT NULL,
  calc_form character varying(500) NOT NULL,
  bbg_pulls_id int[],
  wh_calc_id   int[],
  CONSTRAINT wh_calc_pkey PRIMARY KEY (id),
  CONSTRAINT wh_calc_calc_form_key UNIQUE (calc_form)
);

Then we create a trigger dostuff and install it as wtfever

CREATE FUNCTION dostuff()
    RETURNS trigger
AS $BODY$
DECLARE
    bbg int[];
    wh int[];
BEGIN
    SELECT ARRAY(
        SELECT x[1]
        FROM regexp_matches(new.calc_form, '\[bp(\d+)\]', 'gi') AS x
    )
    INTO bbg;

    SELECT ARRAY(
        SELECT x[1]
        FROM regexp_matches(new.calc_form, '\[wc(\d+)\]', 'gi') AS x
    )
    INTO wh;

    new.wh_calc_id = wh;
    new.bbg_pulls_id = bbg;

    RETURN new;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER wtfever
BEFORE INSERT OR UPDATE ON wh_calc
    FOR EACH ROW EXECUTE PROCEDURE dostuff();

Finally we can test it

INSERT INTO wh_calc (calc_form)
VALUES ('([bp1]/[wc66])*[wc100]');

Now we have,

TABLE wh_calc;
 id |       calc_form        | bbg_pulls_id | wh_calc_id 
----+------------------------+--------------+------------
  1 | ([bp1]/[wc66])*[wc100] | {1}          | {66,100}