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
--------------------------
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 |