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

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 anothe

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:

• Regular Expressions to capture the integers in [bp___] and [wc___]. The regexp_matches function returns a SET of text[].

• The SET of text[] is converted into an array of arrays by means of array_agg.

• 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

StackExchange Database Administrators Q#162678, answer score: 3