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';
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
