Re: Syntax Issue in Trigger Function??

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

tsarevich(at)gmail(dot)com wrote:
> 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?

This looks to me like a candidate:
> v_ref := \'\'/\'\';
Without escaping it looks like v_ref := ''/'';
dividing 2 empty strings, and indeed gives
division by zero in psql. What dividing 2 strings is actually
supposed to mean is not evident form the docs in the first glance.

> v_ref := \'\'/\'\' || v_parent_party_id || v_ref;

This one too.

Andre

Andre Maasikas <andre(dot)maasikas(at)abs(dot)ee> writes:
> Without escaping it looks like v_ref := ''/'';
> dividing 2 empty strings, and indeed gives
> division by zero in psql. What dividing 2 strings is actually
> supposed to mean is not evident form the docs in the first glance.

This is a pet peeve of mine that I unfortunately forgot to do anything
about before 8.0 beta started; so it's too late for this release, unless
there is another reason for forcing initdb before final. The problem is
that the "char" type (not to be confused with CHAR(n) type) has basic
arithmetic operators defined, and since it is considered a member of the
STRING type class, these operators get selected whenever a couple of
undecorated strings are provided.

Try these on for size :-(

regression=# select '2' + '2';
?column?
----------
d
(1 row)

regression=# select 'A' * 'B';
?column?
----------

(1 row)

regression=# select '1' / '';
ERROR: division by zero

Given the one-byte precision, these operators are surely of pretty
marginal use. I'd leave 'em alone if it weren't that the type coercion
rules cause the parser to seize on these operators in cases where a "no
operator could be identified" error would be far more appropriate.

regards, tom lane

Cheers for that! We did catch it eventually. My colleague was using
pgAdminIII and was apparently typing:
v_ref := ''/'';
and pgAdminIII "appears" to have been "helping out" by escaping the
single quotes.

On Tue, 28 Sep 2004 22:46:31 +0300, Andre Maasikas
<andre(dot)maasikas(at)abs(dot)ee> wrote:
> tsarevich(at)gmail(dot)com wrote:
> > 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?
>
> This looks to me like a candidate:
> > v_ref := \'\'/\'\';
> Without escaping it looks like v_ref := ''/'';
> dividing 2 empty strings, and indeed gives
> division by zero in psql. What dividing 2 strings is actually
> supposed to mean is not evident form the docs in the first glance.
>
> > v_ref := \'\'/\'\' || v_parent_party_id || v_ref;
>
> This one too.
>
> Andre
>