Hello,
For lack of a better title, I need to a reverse recursion. Where I have
the parent ID, but then I need to find all it's children, grandchildren,
great-grandchildern, etc for the parent-id. I guess this would be like a
directory and all its sub-directories.
I modified a normal recursive function I got from the cookbook , but it's
impossible for me to decipher where the top level begins and the children
end.
Could someone point me in the right direction to clean this up? I can
provide sample data if needed.
TIA
Patrick Hatcher
-Table
CREATE TABLE mdc_category ( keyp_category int4 NOT NULL, category_name varchar(255), parent_keypcategoryid int4,
CONSTRAINTxpkmdc_category UNIQUE (keyp_category)
)
CREATE FUNCTION category_descriptiontest(int4) RETURNS varchar AS '
DECLARE v_category_id ALIAS FOR $1;
DECLARE tmp_record RECORD;
DECLARE tmp_id VARCHAR;
DECLARE tmp_code VARCHAR;
DECLARE keycheck INT4;
BEGIN
tmp_code:='''';
FOR tmp_record IN SELECT keyp_category,category_name,parent_keypcategoryid
from mdc_category where parent_keypcategoryid = v_category_id LOOP
IF tmp_record.parent_keypcategoryid=0 THEN RETURN tmp_record.keyp_category; END IF;
tmp_id:=category_descriptiontest(tmp_record.keyp_category); IF tmp_record.keyp_category<>0 THEN tmp_code:=
tmp_code|| ''
-'' || tmp_id::varchar || tmp_record.keyp_category::varchar || '' -'';
/* tmp_code:= tmp_code || '''' || tmp_id::varchar || ''
-'' || tmp_record.keyp_category::varchar;
*/ END IF;
END LOOP;
RETURN tmp_code;END; ' LANGUAGE 'plpgsql';
