Обсуждение: need trigger help

Hi All,

I need some trigger help. I am very new to SQL, so please bare with me.

I have a table:

CREATE TABLE table (
    id integer,
    name text,
);

Basically, I have values going into name.

The values that go into name are like this (being generated by a script), without the quotes:
"    name1"
"name2"
"  name3"
"    name4"
"name5   "

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.

I want name to be like this:
"name1"
"name2"
"name3"
"name4"
"name5"


Is that possible?

TIA

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.