Re: need trigger help

On Nov 20, 2007 10:59 AM, Mag Gam <magawake@gmail.com> wrote:
> I want to write a trigger, for UPDATE/INSERT, do a ltrim and rtrim() of name
> value. I want to remove all the beginning and ending blank spaces.

The PostgreSQL docs are really good:

http://www.postgresql.org/docs/8.2/static/plpgsql-trigger.html

Base on the examples from the docs, you could do:

CREATE TABLE T(
    ID INTEGER,
    NAME TEXT
);

CREATE OR REPLACE FUNCTION T_F() RETURNS TRIGGER AS $$
    BEGIN
        IF TG_OP IN ('UPDATE','INSERT') THEN
            NEW.NAME = TRIM(NEW.NAME);
        END IF;
        RETURN NEW;
    END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER T_T
BEFORE INSERT OR UPDATE ON T
FOR EACH ROW EXECUTE PROCEDURE T_F();

-- TEST

INSERT INTO T VALUES (1, '         A           ');

SELECT * FROM T;

Good luck.