Database Administration
postgresql trigger psql
Updated Mon, 18 Jul 2022 01:45:51 GMT

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




Solution

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





Comments (5)

  • +0 – 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. — Jan 30, 2017 at 22:19  
  • +0 – Which version of PostgreSQL are you using? I tested this with 9.6.1. — Jan 30, 2017 at 22:23  
  • +0 – "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. — Jan 30, 2017 at 22:26  
  • +1 – @mountainclimber: modified the response to make it 9.3 compatible. Tested on SQLFiddle. It's actually a bit more clean. — Jan 30, 2017 at 22:43  
  • +0 – Do you have to use array_agg() or can you use ARRAY()? — Jan 30, 2017 at 22:47