CREATE TABLE site2site
(
id serial NOT NULL,
siteid integer,
parentid integer,
)
(
id serial NOT NULL,
siteid integer,
parentid integer,
)
-- Function: getsitechild(id integer)
DROP FUNCTION getsitechild(id integer);
CREATE OR REPLACE FUNCTION getsitechild(id integer)
RETURNS integer[] AS
$BODY$
DECLARE
cids integer[];
childid integer;
BEGIN
cids = array[]::integer[];
for childid in select siteid from site2site where parentid = id loop
cids = cids || childid;
cids = cids || (select * from getsitechild(childid));
end loop;
return cids;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION getsitechild(id integer) OWNER TO postgres;