Re: need trigger help

On Nov 20, 2007 10:59 AM, Mag Gam <magawake(at)gmail(dot)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.