Trigger help - Mailing list pgsql-general

From David Pratt
Subject Trigger help
Date
Msg-id 118F2B41-EBC6-11D9-A19D-000A27B3B070@eastlink.ca
Whole thread Raw
In response to Re: Populating huge tables each day  (Ben-Nes Yonatan <da@canaan.co.il>)
Responses Re: Trigger help Solved
List pgsql-general
Basically I want this trigger to work after a language record in my
languages table is added.

CREATE TRIGGER language_add_trig AFTER INSERT ON languages
    FOR EACH ROW EXECUTE PROCEDURE trigger_language_add();

Here is my function but it is not working.  I am wanting to loop for
each record in my multi_language
table, grab the english text from each record and append and array to
multi dimensional array in
lang_code_and_text field in same record.  I am new to triggers so help
appreciated debugging or
correcting my syntax.

Thanks,
David

CREATE FUNCTION trigger_language_add() RETURNS opaque AS '
    DECLARE

    r record;                     -- record
    en_key int;                   -- holds english key value
    default_text text;            -- holds english text value
    iso_en text := ''en'';        -- holds value of en


    BEGIN
        -- Sets english language key
        SELECT INTO en_key
        ml_key
        FROM languages
        WHERE iso_id = iso_en;

        FOR r in SELECT * from multi_language LOOP

            -- Sets default_text to english text value for record
            SELECT INTO default_text
            lang_code_and_text[en_key][2]
            FROM multi_language
            WHERE id = r.id;

            RAISE NOTICE ''Current record id is %.'', r.id;
            RAISE NOTICE ''Default english text is %.'', default_text;

            -- Appends new language arrray to exisiting multidimensional array
            -- New language array of form {''fr'',''Default english text here''}

            UPDATE multi_language
            SET lang_code_and_text = r.lang_code_and_text || ARRAY[new.iso_id,
default_text]
            WHERE id = r.id;

        END LOOP;
    END;
' LANGUAGE 'plpgsql';

pgsql-general by date:

Previous
From: Ben-Nes Yonatan
Date:
Subject: Re: Populating huge tables each day
Next
From: "Andrus Moor"
Date:
Subject: Re: Hot to restrict access to subset of data