From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Joe Conway <mail@joeconway.com>
Subject: Re: Wierded error in recursive function; debugging ideas?
Date: Wed, 11 Aug 2004 10:58:15 -0700
User-Agent: KMail/1.5.4
Cc: pgsql-sql@postgresql.org
References: <200408102156.25803.josh@agliodbs.com>
	<4119A901.6060003@joeconway.com>
In-Reply-To: <4119A901.6060003@joeconway.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200408111058.15234.josh@agliodbs.com>

Joe,

> There is no in_array() function in Postgres that I'm aware of -- you 
> sure that isn't array_in()?

Yep.  That's a cut-and-paste of the exact log message.

> The rest of that error message doesn't seem 
> to be there in 7.4 either. Can we see the function?

Sure:

CREATE OR REPLACE FUNCTION "sf_event_decendants" (integer,integer)
RETURNS text AS '
DECLARE v_event ALIAS for $1;
	v_user ALIAS for $2;
	child_list INT[];
	sub_child TEXT;
	child_rec RECORD;
	p_status INT;
	contfrom INT;
BEGIN
child_list := ARRAY [ 0 ];
SELECT status INTO p_status
FROM events WHERE event_id = v_event;
IF p_status < 0 THEN
	p_status = -99;
ELSE
	p_status = 0;
END IF;
FOR child_rec IN SELECT event_id FROM events
	WHERE parent_id = v_event AND status > p_status LOOP
	child_list := child_rec.event_id || child_list;
	IF v_user <> 0 THEN
		IF if_get_lock(v_user, ''events'', child_rec.event_id, NULL) <> ''OK'' THEN
			RETURN ''LOCKED:  One or more of the child events of the current event are 
locked by '' ||
				''another user at this time.  You cannot proceed.'';
		END IF;
	END IF;
END LOOP;
FOR child_rec IN SELECT event_id FROM events
	WHERE (continued_id = v_event or event_id = COALESCE(contfrom, 0)) and status 
> p_status LOOP
	child_list := child_rec.event_id || child_list;
	IF v_user <> 0 THEN
		IF if_get_lock(v_user, ''events'', child_rec.event_id, NULL) <> ''OK'' THEN
			RETURN ''LOCKED:  One or more of the child events of the current event are 
locked by '' ||
				''another user at this time.  You cannot proceed.'';
		END IF;
	END IF;
END LOOP;
IF child_list = ARRAY[0] THEN
	RETURN ''0'';
END IF;
FOR child_rec IN SELECT event_id FROM events
				WHERE status > p_status
				AND event_id = ANY ( child_list )
				LOOP
	sub_child := sf_event_decendants(child_rec.event_id, v_user);
	IF sub_child <> ''0'' THEN
		child_list := child_list || string_to_array(sub_child, '','')::INT[];
		IF v_user <> 0 THEN
			IF if_get_lock(v_user, ''events'', child_rec.event_id, NULL) <> ''OK'' THEN
				RETURN ''LOCKED:  One or more of the child events of the current event are 
locked by '' ||
					''another user at this time.  You cannot proceed.'';
			END IF;
		END IF;
	END IF;
END LOOP;
RETURN array_to_string(child_list, '','');
END;' LANGUAGE 'plpgsql';


-- 
-Josh Berkus
 "A developer of Very Little Brain"
 Aglio Database Solutions
 San Francisco