On 25 March 2011 19:13, salah jubeh <s_jubeh@xxxxxxxxx> wrote:
http://www.postgresql.org/docs/current/static/queries-with.htmlHello Guys
The query in this function returns the dependency for level one. However, I need the dependency for all level. I am still new with plpgsql so; how can I use recursive function to return all dependency for all levels
CREATE OR REPLACE FUNCTION dependon (var text) RETURNS SETOF text AS
$BODY$
DECLARE
ÂÂÂ node record;
BEGIN
ÂÂÂ FOR node IN SELECT relname FROM pg_class WHERE OID in (
ÂÂÂ ÂÂÂ SELECT ev_class FROM pg_rewrite, pg_depend
ÂÂÂ ÂÂÂ WHERE pg_depend.objid = pg_rewrite.oid
ÂÂÂ ÂÂÂ AND deptype ='n'
ÂÂÂ ÂÂÂ AND refobjsubid = 1
ÂÂÂ ÂÂÂ AND refobjid::regclass::text = $1)
ÂÂÂ LOOP
ÂÂÂ ÂÂÂ IF node.relname IS NOT NULL THEN
ÂÂÂ ÂÂÂ ÂÂÂ ÂÂÂ
ÂÂÂ ÂÂÂ ÂÂÂ RETURN NEXT depend(node.relname);ÂÂÂ
ÂÂÂ ÂÂÂ ÂÂÂ RETURN NEXT node.relname;
ÂÂÂ ÂÂÂ ÂÂÂ
ÂÂÂ ÂÂÂ END IF;
ÂÂÂ ÂÂÂ
ÂÂÂ END LOOP;
END
$BODY$
LANGUAGE 'plpgsql';
You can do it with "WITH RECURSIVE" without PL/pgSQL:
