Consulta hierarquica no PostgreSQL

[Rodrigo-FS]

Considerando a tabela e os dados abaixo:

create table pessoa (pessoa VARCHAR(15), pai VARCHAR(15));

insert into pessoa values ('pai 1', null);
insert into pessoa values ('pai 2', null);
insert into pessoa values ('filho 1.1', 'pai 1');
insert into pessoa values ('filho 1.2', 'pai 1');
insert into pessoa values ('filho 2.1', 'pai 2');
insert into pessoa values ('filho 1.3', 'pai 1');
insert into pessoa values ('filho 1.4', 'pai 1');
insert into pessoa values ('filho 2.2', 'pai 2');

Gostaria de fazer uma consulta trazendo a hierarquia .... por exemplo no Oracle ... faço dessa forma:

select * from pessoa p
start with pai is null
connect by prior p.pessoa = p.pai;


RESULTADO:

pessoa        pai
-------        -----
pai 1
filho 1.1    pai 1
filho 1.2    pai 1
filho 1.3    pai 1
filho 1.4    pai 1
pai 2
filho 2.1    pai 2
filho 2.2    pai 2

Utilizando o PostgreSQL 9.0, fiz dessa forma:

with recursive p1 (pessoa, pai) as (
    select pessoa, pai from pessoa where pai is null
    union all
    select p.pessoa, p.pai from pessoa p, p1 where p.pai = p1.pessoa
)
select * from p1;

Porém o resultado ficou estranho ... como segue

RESULTADO:

pessoa        pai
-------        -----
pai 1
pai 2
filho 1.1    pai 1
filho 1.2    pai 1
filho 1.3    pai 1
filho 1.4    pai 1

Alguém poderia me ajudar ... preciso que o resultado se apresenta da mesma forma como no Oracle !!

Valeu !

[Richard Marques]

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

Editado Janeiro 21, 2011 por Richard Marques