Re: Funcion recursiva

El jue, 25-10-2007 a las 16:26 +0200, Javier Castro Narváez escribió:
> Hola,
>
> A ver si me podéis echar una manita y orientarme un poco a la hora de
> realizar una función recursiva.
> Dada una tabla con la siguiente estructura:
>
> CREATE TABLE caracteristicas
> (
> id serial NOT NULL,
> id_padre integer NOT NULL DEFAULT 0,
> nombre character varying(50) NOT NULL,
> CONSTRAINT caracteristicas_pkey PRIMARY KEY (id)
> )
> WITH OIDS;
> ALTER TABLE caracteristicas OWNER TO "nfqPeople";
> GRANT ALL ON TABLE caracteristicas TO "nfqPeople";
>
> Que almacena por ejemplo la siguiente información estructurada de forma
> arbórea:
>
> 1;"Color de Ojos";0
> 2;"Negros";1
> 3;"Azules";1
> 4;"Verdes";1
> 6;"Marrones";1
> 7;"Color de Pelo";0
> 8;"Rubio";7
> 9;"Moreno";7
> 10;"Castaño";7
> 11;"Pelirrojo";7
> 12;"Complexión";0
> 13;"Fuerte";12
> 14;"Delgado/a";12
> 15;"Corpulento";12
> 16;"Muy delgado/a";12
> 17;"Gordo";12
>
> Lo que quiero es que la función recorra una deteterminada rama del árbol y
> me devuelva una cadena con el resultado en función de un identificador que
> se le pase. P.E. Caracteristica(11) me devolvería: "Color de Pelo ->
> Pelirrojo"
>
> Programar esto en un lenguaje como c# con una función recursiva no es
> complicado ya que vas realizando múltiples select hasta que el id_padre = 0.
> Trabajando con una variable static para almacenar los resultados
> intermedios. Dado que la estructura permite infinitos niveles padre-hijo, se
> puede llegar a realizar muchísimas consultas. Casi seguro que el rendimiento
> seria infinitamente mejor si lo procesa el propio PostgreSQL.
>
> La verdad es que salvo alguna función simple o un tigger no he programado
> casi nada en Pl/SQL y ando un poco perdido.
>
> Realizar una función recursiva para que devuelva multiples registros no es
> dificil, pero creo, que este no es el caso.
>
> Cualquier orientación será bienvenida.
>
> Un saludo a todos,
> Javier Castro
>
> --
> TIP 1: para suscribirte y desuscribirte, visita http://archives.postgresql.org/pgsql-es-ayuda

Yo uso la funcion de abajo para retornar la ruta completa de una cuenta
de contabilidad. Es algo similar a lo que necesitas.

-- -->>>> DESDE AQUI <<<<<<

-- Function: functions.full_account_name(cod2 text)

-- DROP FUNCTION functions.full_account_name(cod2 text);

CREATE OR REPLACE FUNCTION functions.full_account_name(cod2 text)
RETURNS character varying AS
$BODY$

DECLARE v_account_id ALIAS FOR $1;
DECLARE tmp_record RECORD;
DECLARE tmp_id VARCHAR;
DECLARE tmp_name VARCHAR;

BEGIN
tmp_name:='';

SELECT INTO tmp_record * FROM accounting.charts_titles
WHERE accounting.charts_titles.hcode = v_account_id;

IF NOT FOUND THEN
RETURN ''::varchar;
END IF;

IF tmp_record.hcode = NULL THEN
RETURN tmp_record.hdenom;
END IF;

tmp_id := full_account_name(tmp_record.pcode);

IF tmp_record.hcode IS NOT NULL THEN
tmp_name := tmp_id::varchar || ' / ' || tmp_record.hdenom::varchar
|| '';
END IF;

RETURN tmp_name;
END;

$BODY$
LANGUAGE 'plpgsql' VOLATILE;

-- >>>>> HASTA AQUI <<<

La tabla a la que accede es :

-- Table: accounting.charts_titles

-- DROP TABLE accounting.charts_titles;

CREATE TABLE accounting.charts_titles
(
hcode character varying(15) NOT NULL,
pcode character varying(15),
hdenom character varying(120) NOT NULL,
CONSTRAINT charts_titles_pkey PRIMARY KEY (hcode)
)
WITHOUT OIDS;

-- Index: accounting.charts_titles_by_pcode

-- DROP INDEX accounting.charts_titles_by_pcode;

CREATE INDEX charts_titles_by_pcode
ON accounting.charts_titles
USING btree
(pcode);

donde hcode es id y pcode es id_padre. El registro que no tiene padre,
debe tener id_padre = NULL. Con esto deberias poder adaptarla a tus
necesidades.

Saludos,
Diego.