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_
– JJ_, Commented Sep 16, 2019 at 10:28

S-Man · Accepted Answer · 2019-09-16 11:05:27Z

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, 2019 at 11:05

answered Sep 16, 2019 at 10:31

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

2 Comments

JJ_ Over a year ago

It's very weird that size check is executed before before event. All databases work that way?

S-Man Over a year ago

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

Collectives™ on Stack Overflow

How to register trigger in PostgreSQL?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related