PostgreSQL – Creating Trigger to Autopopulate Fields with Sub-strings

postgresqlpsqltrigger

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.

Best Answer

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

Related Question