consegui algo parecido usando uma função: tabela CREATE TABLE org_fnsp
(
id serial NOT NULL,
nome_org character varying(255),
nivel integer,
uf character varying(2),
endereco character varying(255),
fone character varying(13),
fax character varying(13),
email character varying(100),
fone2 character varying(13),
cidade character varying(50),
cep character varying(8),
sigla character varying(15),
CONSTRAINT org_fnsp_pkey PRIMARY KEY (id)
)
funções::
-- Function: getsubord(integer)
-- DROP FUNCTION getsubord(integer);
CREATE OR REPLACE FUNCTION getsubord(integer)
RETURNS SETOF bdh_subordinacao AS
$BODY$DECLARE
root ALIAS FOR $1;
tempRow bdh_subordinacao%ROWTYPE;
BEGIN
FOR tempRow IN SELECT bdh_subordinacao_opm, bdh_subordinacao_opm_superior FROM getsubord(root, 0) LOOP
RETURN NEXT tempRow;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION getsubord(integer) OWNER TO postgres;
-- Function: getsubord(integer, integer)
-- DROP FUNCTION getsubord(integer, integer);
CREATE OR REPLACE FUNCTION getsubord(integer, integer)
RETURNS SETOF bdh_subordinacao AS
$BODY$DECLARE
root ALIAS FOR $1;
depth ALIAS FOR $2;
tempRow1 bdh_subordinacao%ROWTYPE;
tempRow2 bdh_subordinacao%ROWTYPE;
BEGIN
-- Using PostgreSQL 7.3.4.
-- Docs: [url=http://www.postgresql.org/docs/7.3/static/plpgsql.html]http://www.postgresql.org/docs/7.3/static/plpgsql.html[/url]
-- See chapter 19, especially 19.6
FOR tempRow1 IN SELECT bdh_subordinacao_opm, bdh_subordinacao_opm_superior FROM bdh_subordinacao WHERE bdh_subordinacao_opm_superior = root
LOOP
RETURN NEXT tempRow1;
FOR tempRow2 IN SELECT bdh_subordinacao_opm, bdh_subordinacao_opm_superior FROM
getsubord(tempRow1.bdh_subordinacao_opm, depth+1) LOOP
RETURN NEXT tempRow2;
END LOOP;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION getsubord(integer, integer) OWNER TO postgres;
-- View: organograma
-- DROP VIEW organograma;
CREATE OR REPLACE VIEW organograma AS
SELECT getsubord.bdh_subordinacao_opm, getsubord.bdh_subordinacao_opm_superior, org_fnsp.id, org_fnsp.nome_org, org_fnsp.nivel
FROM getsubord(1) getsubord(bdh_subordinacao_opm, bdh_subordinacao_opm_superior, id)
LEFT JOIN org_fnsp ON org_fnsp.id = getsubord.bdh_subordinacao_opm;
ALTER TABLE organograma OWNER TO postgres; espero que seja isto que você procurava