Syntax Issue in Trigger Function??

From: <tsarevich(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Syntax Issue in Trigger Function??
Date: 2004-09-27 16:42:42
Message-ID: 156a90fe040927094241f35358@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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();
===============================================

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kevin Murphy 2004-09-27 17:38:53 Re: using COPY table FROM STDIN within script run as psql
Previous Message Bruce Momjian 2004-09-27 15:46:52 Re: limiting execution time of queries