function problem plpgsql

From: "Ben-Nes Michael" <miki(at)canaan(dot)co(dot)il>
To: "postgresql" <pgsql-general(at)postgresql(dot)org>
Subject: function problem plpgsql
Date: 2003-03-02 18:30:09
Message-ID: 025701c2e0e9$c17187c0$aa0f5ac2@canaan.co.il
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi All

The following function used to work before I upgraded to 7.3.2 from 7.3.1

from some reason the delete don't delete anything :(

maybe something wrong with the delete statement ?
Can I use GET DIAGNOSTICS with the DELETE statement ?

I tried to run it by hand and it was successful.

Thanks in advance

CREATE FUNCTION forum_delete_topic(INT) RETURNS INTEGER AS '
DECLARE
return_value INTEGER;
lft_rgt forum_tree%ROWTYPE;
v_node_id ALIAS FOR $1;

BEGIN
SELECT INTO lft_rgt lft, rgt, f_id FROM forum_tree WHERE node_id =
v_node_id;

DELETE FROM forum_tree WHERE lft BETWEEN lft_rgt.lft AND lft_rgt.rgt AND
f_id = lft_rgt.f_id;

GET DIAGNOSTICS return_value = ROW_COUNT;

IF return_value < 1 THEN
RETURN 0;
END IF;

UPDATE forum_tree
SET lft = CASE WHEN lft > lft_rgt.lft
THEN lft - ( lft_rgt.rgt - lft_rgt.lft + 1 )
ELSE lft END,
rgt = CASE WHEN rgt >= lft_rgt.lft
THEN rgt - ( lft_rgt.rgt - lft_rgt.lft + 1 )
ELSE rgt END WHERE rgt >= lft_rgt.lft AND f_id =
lft_rgt.f_id;

-- clone the view list_child to real dum table for speed
PERFORM mirror_forum_list_child();

RETURN return_value;

END;'
LANGUAGE 'plpgsql';

--------------------------
Canaan Surfing Ltd.
Internet Service Providers
Ben-Nes Michael - Manager
Tel: 972-4-6991122
Fax: 972-4-6990098
http://sites.canaan.co.il
--------------------------

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mark Tessier 2003-03-02 18:48:32 pg_relcheck
Previous Message Tom Lane 2003-03-02 18:10:09 Re: Doing multiple steps at once