Re: Ayuda en consulta complicada

Hola Amigos:

Tengo la siguiente tabla Dependencia:

depe_id Descipcion Depende
1 AAAAA Null
2 bbbbbb Null
3 xxxxxx Null
4 yyyyyyyy 2
5 zzzzzzz 4

Quiero crear un procedimiento al cual le envíe como parámetro el depe_id y
me devuelva todos los registros que
dependen de ese depe_id. Por ejm si llamo a la función así:
select * from myfuncion(2)
que me devuelva el siguiente cursor:

depe_id Descipcion Depende
2 bbbbbb Null
4 yyyyyyyy 2
5 zzzzzzz 4

--
atte.
Luis Guevara

Luis Guevara escribió:
> Hola Amigos:
>
> Tengo la siguiente tabla Dependencia:
>
> depe_id Descipcion Depende
> 1 AAAAA Null
> 2 bbbbbb Null
> 3 xxxxxx Null
> 4 yyyyyyyy 2
> 5 zzzzzzz 4
>
> Quiero crear un procedimiento al cual le envíe como parámetro el depe_id y
> me devuelva todos los registros que
> dependen de ese depe_id. Por ejm si llamo a la función así:
> select * from myfuncion(2)
> que me devuelva el siguiente cursor:
>
> depe_id Descipcion Depende
> 2 bbbbbb Null
> 4 yyyyyyyy 2
> 5 zzzzzzz 4

Creo que lo mas sencillo seria una funcion recursiva, algo asi (ojo, no
he probado que este correcta. Ni siquiera se si compila. Haz las
correcciones necesarias y despues nos la envias de vuelta):

create function func(int) returns setof dependencia language plpgsql as
$$
declare
foo record;
begin;
select into foo depe_id, descripcion, depende
from dependencia
where depe_id = $1;

if not found
return;
end if;

if (foo.depe_id = foo.depende) then
raise exception 'soy un idiota';
end if;

return next foo;

if foo.depende is not null then
for select into foo depe_id, descripcion, depende
from func(foo.depende) loop
return next foo;
end loop;
end if;

return;
end;
$$;

--
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
"The Postgresql hackers have what I call a "NASA space shot" mentality.
Quite refreshing in a world of "weekend drag racer" developers."
(Scott Marlowe)

Gracias Alvaro,

Estoy compilando la función y ya corregí unas cuantas comas pero ahora me
sale el siguiente error al compilar

ERROR: syntax error en o cerca de «select»

Solo se que hay un error en el SELECT que se encuentra dentro del FOR, pero
no lo ubico.

Por favor espero que puedas ayudarme.

Gracias

2006/1/20, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>:
>
> Luis Guevara escribió:
> > Hola Amigos:
> >
> > Tengo la siguiente tabla Dependencia:
> >
> > depe_id Descipcion Depende
> > 1 AAAAA Null
> > 2 bbbbbb Null
> > 3 xxxxxx Null
> > 4 yyyyyyyy 2
> > 5 zzzzzzz 4
> >
> > Quiero crear un procedimiento al cual le envíe como parámetro el depe_id
> y
> > me devuelva todos los registros que
> > dependen de ese depe_id. Por ejm si llamo a la función así:
> > select * from myfuncion(2)
> > que me devuelva el siguiente cursor:
> >
> > depe_id Descipcion Depende
> > 2 bbbbbb Null
> > 4 yyyyyyyy 2
> > 5 zzzzzzz 4
>
> Creo que lo mas sencillo seria una funcion recursiva, algo asi (ojo, no
> he probado que este correcta. Ni siquiera se si compila. Haz las
> correcciones necesarias y despues nos la envias de vuelta):
>
> create function func(int) returns setof dependencia language plpgsql as
> $$
> declare
> foo record;
> begin;
> select into foo depe_id, descripcion, depende
> from dependencia
> where depe_id = $1;
>
> if not found
> return;
> end if;
>
> if (foo.depe_id = foo.depende) then
> raise exception 'soy un idiota';
> end if;
>
> return next foo;
>
> if foo.depende is not null then
> for select into foo depe_id, descripcion, depende
> from func(foo.depende) loop
> return next foo;
> end loop;
> end if;
>
> return;
> end;
> $$;
>
> --
> Alvaro Herrera
> http://www.amazon.com/gp/registry/CTMLCN8V17R4
> "The Postgresql hackers have what I call a "NASA space shot" mentality.
> Quite refreshing in a world of "weekend drag racer" developers."
> (Scott Marlowe)
>

--
atte.
Luis Guevara

On 1/20/06, Luis Guevara <luis(dot)a(dot)guevara(at)gmail(dot)com> wrote:
> Gracias Alvaro,
>
> Estoy compilando la función y ya corregí unas cuantas comas pero ahora me
> sale el siguiente error al compilar
>
> ERROR: syntax error en o cerca de «select»
>
> Solo se que hay un error en el SELECT que se encuentra dentro del FOR, pero
> no lo ubico.
>
> Por favor espero que puedas ayudarme.
>
> Gracias

if foo.depende is not null then
- for select into foo depe_id, descripcion, depende
+ for foo in select depe_id, descripcion, depende
from func(foo.depende) loop
return next foo;
end loop;
end if;

saca la linea marcada con (-) y cambiala por la linea marcada con (+)

--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Gracias Jaime:

Ya compilé. pero al llamar a la función con

select * from test(2)

me devuelve el siguiente error:

ERROR: wrong record type supplied in RETURN NEXT
CONTEXT: PL/pgSQL function "test" line 12 at return next

Al parecer hay un error con el tipo de dato que devuelve RECORD, porque no
va a devolver un solo registro sino varios registros.

La función ha quedado compilada así:

CREATE OR REPLACE FUNCTION "public"."test" (integer) RETURNS SETOF
"public"."dependencia" AS
$body$
declare
foo record;
begin
select into foo depe_id, depe_nombre, depe_depeid
from dependencia
where depe_id = $1;

if not found then
return;
end if;

return next foo;

if foo.depe_depeid is not null then
for foo in select depe_id, depe_nombre, depe_depeid
from test(foo.depe_depeid) loop
return next foo;
end loop;
end if;

return;
end
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

¿Cómo podría arreglar esto?

Gracias por su apoyo.

El día 20/01/06, Jaime Casanova <systemguards(at)gmail(dot)com> escribió:
>
> On 1/20/06, Luis Guevara <luis(dot)a(dot)guevara(at)gmail(dot)com> wrote:
> > Gracias Alvaro,
> >
> > Estoy compilando la función y ya corregí unas cuantas comas pero ahora
> me
> > sale el siguiente error al compilar
> >
> > ERROR: syntax error en o cerca de «select»
> >
> > Solo se que hay un error en el SELECT que se encuentra dentro del FOR,
> pero
> > no lo ubico.
> >
> > Por favor espero que puedas ayudarme.
> >
> > Gracias
>
>
> if foo.depende is not null then
> - for select into foo depe_id, descripcion, depende
> + for foo in select depe_id, descripcion, depende
> from func(foo.depende) loop
> return next foo;
> end loop;
> end if;
>
>
> saca la linea marcada con (-) y cambiala por la linea marcada con (+)
>
>
> --
> Atentamente,
> Jaime Casanova
> (DBA: DataBase Aniquilator ;)
>

--
atte.
Luis Guevara

On 1/20/06, Luis Guevara <luis(dot)a(dot)guevara(at)gmail(dot)com> wrote:
> Gracias Jaime:
>
> Ya compilé. pero al llamar a la función con
>
> select * from test(2)
>
> me devuelve el siguiente error:
>
> ERROR: wrong record type supplied in RETURN NEXT
> CONTEXT: PL/pgSQL function "test" line 12 at return next
>
> Al parecer hay un error con el tipo de dato que devuelve RECORD, porque no
> va a devolver un solo registro sino varios registros.
>
> La función ha quedado compilada así:
>
> CREATE OR REPLACE FUNCTION "public"."test" (integer) RETURNS SETOF
> "public"."dependencia" AS
> $body$
> declare
> foo record;
> begin
> select into foo depe_id, depe_nombre, depe_depeid
> from dependencia
> where depe_id = $1;
>
>
> if not found then
> return;
> end if;
>
> return next foo;
>
> if foo.depe_depeid is not null then
> for foo in select depe_id, depe_nombre, depe_depeid
> from test(foo.depe_depeid) loop
> return next foo;
> end loop;
> end if;
>
> return;
> end
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
> ¿Cómo podría arreglar esto?
>
> Gracias por su apoyo.
>
>

a mi si me funciona...
seguro que los campos de tu tabla son depe_id, depe_nombre, depe_depeid

--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Si esos son los campos, con toda seguridad, el problema es que me devuelve
este error:

ERROR: wrong record type supplied in RETURN NEXT
CONTEXT: PL/pgSQL function "test" line 12 at return next

Ojo que lo que deseo es obtener una lista de todos los registros que
dependen de una dependencia. Entonces
el resultado debe ser un grupo de registros y no un solo registro. Por eso
no entiendo porque en la funcion se ha puesto que devuelva un tipo de dato
RECORD, si ese tipo de dato, tengo entendido que solo devuelve una fila.

Espero puedas seguir ayudandome.

Gracias

El día 20/01/06, Jaime Casanova <systemguards(at)gmail(dot)com> escribió:
>
> On 1/20/06, Luis Guevara <luis(dot)a(dot)guevara(at)gmail(dot)com> wrote:
> > Gracias Jaime:
> >
> > Ya compilé. pero al llamar a la función con
> >
> > select * from test(2)
> >
> > me devuelve el siguiente error:
> >
> > ERROR: wrong record type supplied in RETURN NEXT
> > CONTEXT: PL/pgSQL function "test" line 12 at return next
> >
> > Al parecer hay un error con el tipo de dato que devuelve RECORD, porque
> no
> > va a devolver un solo registro sino varios registros.
> >
> > La función ha quedado compilada así:
> >
> > CREATE OR REPLACE FUNCTION "public"."test" (integer) RETURNS SETOF
> > "public"."dependencia" AS
> > $body$
> > declare
> > foo record;
> > begin
> > select into foo depe_id, depe_nombre, depe_depeid
> > from dependencia
> > where depe_id = $1;
> >
> >
> > if not found then
> > return;
> > end if;
> >
> > return next foo;
> >
> > if foo.depe_depeid is not null then
> > for foo in select depe_id, depe_nombre, depe_depeid
> > from test(foo.depe_depeid) loop
> > return next foo;
> > end loop;
> > end if;
> >
> > return;
> > end
> > $body$
> > LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
> >
> > ¿Cómo podría arreglar esto?
> >
> > Gracias por su apoyo.
> >
> >
>
> a mi si me funciona...
> seguro que los campos de tu tabla son depe_id, depe_nombre, depe_depeid
>
> --
> Atentamente,
> Jaime Casanova
> (DBA: DataBase Aniquilator ;)
>

--
atte.
Luis Guevara

On 1/20/06, Luis Guevara <luis(dot)a(dot)guevara(at)gmail(dot)com> wrote:
> Si esos son los campos, con toda seguridad, el problema es que me devuelve
> este error:
>
> ERROR: wrong record type supplied in RETURN NEXT
> CONTEXT: PL/pgSQL function "test" line 12 at return next
>

* crea una tabla dependencia con la siguiente estructura...

pruebas=# \d dependencia
Tabla «public.dependencia»
Columna | Tipo | Modificadores
-------------+---------+---------------
depe_id | integer |
depe_nombre | text |
depe_depeid | integer |

* copie, pegue y compile la funcion tal como la mostraste en el mail anterior...

CREATE OR REPLACE FUNCTION "public"."test" (integer) RETURNS SETOF
"public"."dependencia" AS
$body$
declare
foo record;
begin
select into foo depe_id, depe_nombre, depe_depeid
from dependencia
where depe_id = $1;
if not found then
return;
end if;

return next foo;

if foo.depe_depeid is not null then
for foo in select depe_id, depe_nombre, depe_depeid
from test(foo.depe_depeid) loop
return next foo;
end loop;
end if;

return;
end
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

* ejecute el select de esta forma

pruebas=# select * from test(5);
depe_id | depe_nombre | depe_depeid
---------+-------------+-------------
5 | zzzzz | 4
4 | yyyyy | 2
2 | bbbbb |
(3 filas)

no veo el problema, que version de postgres tienes?

> Ojo que lo que deseo es obtener una lista de todos los registros que
> dependen de una dependencia. Entonces

Para eso es el SETOF que incluyo Alvaro en la definicion de la funcion

> el resultado debe ser un grupo de registros y no un solo registro. Por eso
> no entiendo porque en la funcion se ha puesto que devuelva un tipo de dato
> RECORD, si ese tipo de dato, tengo entendido que solo devuelve una fila.
>

no devuelve RECORD, devuelve SETOF RECORD

ademas el tipo de dato no es el que devuelve algo, es la funcion la
que devuelve valores y por lo tanto es a la funcion a la que hay que
indicarle que debe regresar varios registros de ese tipo de datos...

--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Ok. Jaime he probado tal como indicas y funciona.

Pero no era lo que habia pedido, yo necesitaba que a partir de un id que se
envie como parámetro obtener todos los que dependen de este id (es decir
todos sus hijos) y no DE QUIEN DEPENDE o de donde procede ese ID.

Pero de todas maneras muchas gracias especialmente a Jaime y Alvaro.

La función que me brindarón me sirvió mucho para aprender y sacar otras
ideas.

Finalmente, despues de tanto probar he logrado desarrollar la función que
quería. Espero que le pueda servir a alguién. Ha quedado así:

CREATE OR REPLACE FUNCTION "public"."test2" (integer) RETURNS SETOF
"public"."dependencia" AS
$body$
declare
tabla dependencia%ROWTYPE;
begin
select into tabla depe_id from dependencia where depe_id = $1;
if not found then
return;
end if;
return next tabla;

for tabla in select depe_id from dependencia where depe_depeid = $1
loop
for tabla in select depe_id
from test2(tabla.depe_id) loop
return next tabla;
end loop;
end loop;
end
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
Aun me quedan algunas dudas en cuanto a RECORD y %ROWTYPE y he tenido que
hacerlo con el último para que funcione porque ya no tengo tiempo en estos
momentos para seguir probando.

Gracias a todos.

El día 20/01/06, Jaime Casanova <systemguards(at)gmail(dot)com> escribió:
>
> On 1/20/06, Luis Guevara <luis(dot)a(dot)guevara(at)gmail(dot)com> wrote:
> > Si esos son los campos, con toda seguridad, el problema es que me
> devuelve
> > este error:
> >
> > ERROR: wrong record type supplied in RETURN NEXT
> > CONTEXT: PL/pgSQL function "test" line 12 at return next
> >
>
> * crea una tabla dependencia con la siguiente estructura...
>
> pruebas=# \d dependencia
> Tabla «public.dependencia»
> Columna | Tipo | Modificadores
> -------------+---------+---------------
> depe_id | integer |
> depe_nombre | text |
> depe_depeid | integer |
>
>
> * copie, pegue y compile la funcion tal como la mostraste en el mail
> anterior...
>
> CREATE OR REPLACE FUNCTION "public"."test" (integer) RETURNS SETOF
> "public"."dependencia" AS
> $body$
> declare
> foo record;
> begin
> select into foo depe_id, depe_nombre, depe_depeid
> from dependencia
> where depe_id = $1;
> if not found then
> return;
> end if;
>
> return next foo;
>
> if foo.depe_depeid is not null then
> for foo in select depe_id, depe_nombre, depe_depeid
> from test(foo.depe_depeid ) loop
> return next foo;
> end loop;
> end if;
>
> return;
> end
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
> * ejecute el select de esta forma
>
> pruebas=# select * from test(5);
> depe_id | depe_nombre | depe_depeid
> ---------+-------------+-------------
> 5 | zzzzz | 4
> 4 | yyyyy | 2
> 2 | bbbbb |
> (3 filas)
>
>
> no veo el problema, que version de postgres tienes?
>
>
> > Ojo que lo que deseo es obtener una lista de todos los registros que
> > dependen de una dependencia. Entonces
>
> Para eso es el SETOF que incluyo Alvaro en la definicion de la funcion
>
> > el resultado debe ser un grupo de registros y no un solo registro. Por
> eso
> > no entiendo porque en la funcion se ha puesto que devuelva un tipo de
> dato
> > RECORD, si ese tipo de dato, tengo entendido que solo devuelve una fila.
>
> >
>
> no devuelve RECORD, devuelve SETOF RECORD
>
> ademas el tipo de dato no es el que devuelve algo, es la funcion la
> que devuelve valores y por lo tanto es a la funcion a la que hay que
> indicarle que debe regresar varios registros de ese tipo de datos...
>
>
> --
> Atentamente,
> Jaime Casanova
> (DBA: DataBase Aniquilator ;)
>

--
atte.
Luis Guevara

Luis Guevara escribió:
> Ok. Jaime he probado tal como indicas y funciona.
>
> Pero no era lo que habia pedido, yo necesitaba que a partir de un id que se
> envie como parámetro obtener todos los que dependen de este id (es decir
> todos sus hijos) y no DE QUIEN DEPENDE o de donde procede ese ID.

Entonces tu ejemplo inicial estaba malo ...

--
Alvaro Herrera http://www.PlanetPostgreSQL.org
"Java is clearly an example of money oriented programming" (A. Stepanov)

Acabo de revisarlo nuevamente y sí está bien, ojo que yo planteo llamar a la
función:

select * from myfuncion(2)

Osea todos los hijos del ID 2

y no

select * from myfuncion(5)

De todas maneras muchas gracias, la función que me diste fue mi punto de
partida, sin esta me hubiera sido muy dificil obtener la función final, no
tenia conocimiento que se podían hacer ese tipo de operaciones en un
procedimiento.

El día 20/01/06, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> escribió:

> Luis Guevara escribió:
> > Ok. Jaime he probado tal como indicas y funciona.
> >
> > Pero no era lo que habia pedido, yo necesitaba que a partir de un id que
> se
> > envie como parámetro obtener todos los que dependen de este id (es decir
> > todos sus hijos) y no DE QUIEN DEPENDE o de donde procede ese ID.
>
> Entonces tu ejemplo inicial estaba malo ...
>
> --
> Alvaro Herrera
> http://www.PlanetPostgreSQL.org
> "Java is clearly an example of money oriented programming" (A. Stepanov)
>

--
atte.
Luis Guevara