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

• mountainclimber11: Thank you very much for the detailed answer. I'm getting this error: "ERROR: could not find array type for data type text[] CONTEXT: SQL statement "SELECT array_agg(x) FROM regexp_matches(new.calc_form, '[bp(\d+)]', 'gi') AS x" PL/pgSQL function wh_calc_ins_upd_trg_func() line 5 at SQL statement" I'm still looking into this.
• joanolo: Which version of PostgreSQL are you using? I tested this with 9.6.1.
• mountainclimber11: "PostgreSQL 9.3.5 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 4.8.3 20140627 [gcc-4_8-branch revision 212064], 64-bit" Looks like I am a few back.
• joanolo: @mountainclimber: modified the response to make it 9.3 compatible. Tested on SQLFiddle. It's actually a bit more clean.
• Evan Carroll: Do you have to use array_agg() or can you use ARRAY()?

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}

• mountainclimber11: It seems like you are correct on all fronts. Thank you for the suggestions, but I'll have to think on this a bit to sort out how to implement it.
• joanolo: @mountainclimber: This would make your table more clear and flexible . It is also less SQL standard, if that might matter; although I must confess the functions used in the trigger I proposed are also non-standard at all.
• Evan Carroll: I think it's standard SQL. The PostgreSQL array is in the SQL spec, regardless of how many databases implement it.
• joanolo: You're right. Didn't know it was actually in the spec. Thx.
• Evan Carroll: I don't care in the slightest about that. I'm just glad you picked an answer and upvoted the ones you found useful. You get to decide which one is correct.