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
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.