Thread: function problem plpgsql

function problem plpgsql

From

"Ben-Nes Michael"

Date:

02 March 2003, 16:31:36

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

Re: function problem plpgsql

From

Dennis Gearon

Date:

02 March 2003, 23:45:09

What's the error message?
--

Carpe Dancem ;-)
-----------------------------------------------------------------
Remember your friends while they are alive
-----------------------------------------------------------------
                         Sincerely, Dennis Gearon

Re: function problem plpgsql

From

"Ben-Nes Michael"

Date:

03 March 2003, 07:14:46

No error, it just dont delete, and exit with the result of 0
--------------------------
Canaan Surfing Ltd.
Internet Service Providers
Ben-Nes Michael - Manager
Tel: 972-4-6991122
Fax: 972-4-6990098
http://sites.canaan.co.il
--------------------------
----- Original Message -----
From: "Dennis Gearon" <gearond@cvc.net>
To: "Ben-Nes Michael" <miki@canaan.co.il>
Cc: "postgresql" <pgsql-general@postgresql.org>
Sent: Monday, March 03, 2003 3:45 AM
Subject: Re: [GENERAL] function problem plpgsql


> What's the error message?
> --
>
> Carpe Dancem ;-)
> -----------------------------------------------------------------
> Remember your friends while they are alive
> -----------------------------------------------------------------
>          Sincerely, Dennis Gearon
>