Syntax Issue in Trigger Function??
От | |
---|---|
Тема | Syntax Issue in Trigger Function?? |
Дата | |
Msg-id | 156a90fe040927094241f35358@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Syntax Issue in Trigger Function??
|
Список | pgsql-general |
In Postgres 7.3.5 - When we try to insert a new record into our parties.party table which is then meant to fire off a trigger to update a column in the table with some de-normalised information, we get the following error: ERROR: FLOATING POINT EXCEPTION! The last floating point operation either exceeded the legal ranges or was a divide by zero. Can someone help spot our syntax erorr, please? Many thanks! =============================================== CREATE TABLE parties.party ( party_id serial NOT NULL, parent_party_id int4, party_type char(1) NOT NULL, party_name text NOT NULL, party_path text, modified_by text, modified_dtm timestamp, created_by text, created_dtm timestamp ); =============================================== CREATE OR REPLACE FUNCTION parties.update_party_ref() RETURNS trigger AS ' DECLARE v_party_id INTEGER; v_parent_party_id INTEGER; v_ref TEXT; BEGIN /* from the end to the beginning (i.e backwards) navigate up the tree of parties adding the party ids separated by the backslash character */ -- we always start with backslash v_ref := \'\'/\'\'; -- grab the first party id to look at v_party_id := new.party_id; -- set the loop up with an initial read SELECT INTO v_parent_party_id parent_party_id FROM parties.party WHERE party_id = v_party_id AND parent_party_id IS NOT NULL; WHILE FOUND LOOP -- prefix the ref weve already accumulated with backslash followed by the parent party id v_ref := \'\'/\'\' || v_parent_party_id || v_ref; -- the parent party id now becomes the party id one level up v_party_id := v_parent_party_id; -- look for more parents SELECT INTO v_parent_party_id parent_party_id FROM parties.party WHERE party_id = v_party_id AND parent_party_id IS NOT NULL; END LOOP; -- now we can perform the update update parties.party set party_path = v_ref; RETURN NULL; END; ' LANGUAGE 'plpgsql' VOLATILE; =============================================== CREATE TRIGGER trg_update_party_ref AFTER INSERT OR UPDATE ON parties.party FOR EACH ROW EXECUTE PROCEDURE parties.update_party_ref(); ===============================================
В списке pgsql-general по дате отправления: