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();
===============================================
