How to register trigger in PostgreSQL?

Question

I have trigger and procedure. I want to be sure that string saved to database will have no '-' characters.

After executing

UPDATE interesant SET interesant_nip = '555-555-5555'

I get error

value is too long for varchar(10).

This suggests that '-' characters were not removed before insert-update.

Why is my trigger not being executed?

CREATE FUNCTION normalize_nip() RETURNS TRIGGER AS $$
BEGIN
    -- replace '' to NULL
    IF (NEW.interesant_nip LIKE '') THEN
        NEW.interesant_nip := NULL;
        RETURN NEW;
    END IF;

    -- remove '-' from string
    NEW.interesant_nip := REPLACE(NEW.interesant_nip, '-', '');

    RETURN NEW;
END; $$ LANGUAGE plpgsql;"

CREATE TRIGGER interesant_nip_normalize BEFORE INSERT OR UPDATE ON public.interesant FOR EACH ROW EXECUTE PROCEDURE normalize_nip()

@Lucas 'after' is too late, saved value will be too big for this field. — JJ_, Sep 16 '19 at 10:28

S-Man · Accepted Answer · 2019-09-16T11:05:27.710

1

The updated or inserted value is always treated as type varchar(10) - before and after the trigger function. So, you cannot handle the value because the input type does not fit the value even if the trigger function converts it into a valid one.

It works if you have an unbounded text type. There you can see that the trigger is executed:

demo:db<>fiddle

So, the only chance to handle this, is, to normalize it before inserting or updating:

demo:db<>fiddle

INSERT INTO interesant VALUES (normalize_nip('555-555-5555'));

UPDATE interesant SET interesant_nip = normalize_nip('555-555-5555')

edited Sep 16 '19 at 11:05

answered Sep 16 '19 at 10:31

S-Man

22,521
7
40
63

It's very weird that size check is executed before `before` event. All databases work that way? – JJ_ Sep 16 '19 at 11:25
1

It's not the size, it is the **type**. Let's assume another example: You have an int column. And before inserting you always want to add +10. So the trigger takes the input and does the addition. But suddenly you are putting in a text value "abc". This wouldn't work because the trigger assumes an integer value. It would fail as well. That's exactly the same case because '555-555-5555' is a different type than varchar(10). – S-Man Sep 16 '19 at 11:37

How to register trigger in PostgreSQL?

1 Answers1