PostgreSQL Trigger Function Update

Question

I have a table where column sub is first digit from column id.

    id   |  sub  |
---------+-------+
      21 |   2   |
      32 |   3   |
      43 |   4   |

I want to create a trigger that updates sub when id is changed.

Below is an example of the result I want:

    id   |  sub  |
---------+-------+
      21 |   2   |
      22 |   2   |
      43 |   4   |

In the second row, the value for id has changed from '32' to '22', so the sub must change from '3' to '2'.

Can someone please help me solve my problem?

Gab · Accepted Answer · 2017-03-13 02:27:45Z

1

Here is a sample schema based on your example:

CREATE TABLE t (id serial, sub int);
INSERT INTO t (sub) values (null);
INSERT INTO t (sub) values (null);
INSERT INTO t (sub) values (null);

You would use this function and trigger:

CREATE OR REPLACE FUNCTION setSub() RETURNS TRIGGER AS $$
BEGIN
    NEW.sub := substring(CAST(NEW.id as CHAR(6)), 1, 1);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER setSub
BEFORE INSERT OR UPDATE ON t
FOR EACH ROW EXECUTE PROCEDURE setSub();

Usage example:

test=# SELECT * FROM t;
 id | sub 
----+-----
  1 |    
  2 |    
  3 |    
(3 rows)

test=# UPDATE t SET id = 3000 WHERE id = 1;
UPDATE 1
test=# SELECT * FROM t;
  id  | sub 
------+-----
    2 |    
    3 |    
 3000 |   3
(3 rows)

test=#

answered Mar 13, 2017 at 2:27

Gab

3,5381 gold badge13 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL Trigger Function Update

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related