Re: Ayuda con funcion record

2006/6/10, jose arellano <josearellanov(at)gmail(dot)com>:
>
> hola a todos, espero k me puedan ayudar, tengo k devolver tuplas de una
> funcion, pero al ejecutar la funcion me da un error:
>
> create or replace function contador() returns setof record as'
>
> DECLARE
>
> query2 record;
>
> BEGIN
>
>
> FOR query2 IN select cc.nombre as centro_costo,
> (select count(pre.id)
> from detalle_movimiento as det
> inner join prescripcion as pre on pre.id = det.id_prescripcion
> and pre.id_centro = det.id_centro
> inner join movimiento as m on det.id_movimiento = m.id
> and det.id_centro = m.id_centro
> inner join receta as r on pre.id_receta = r.id and pre.id_centro =
> r.id_centro
> inner join atencion as at on r.id_atencion = at.id and r.id_centro
> = at.id_centro
> inner join paciente as pac on at.rut_paciente = pac.rut_persona
> and at.id_centro = pac.id_centro
> inner join persona as pers on pac.rut_persona = pers.rut
> and pac.id_centro = pers.id_centro
> where (pre.cantidad = pre.cantidad_despachada)
> and (m.id_centro_costo in
>
> (11,12,15,13,20,29,23,31,24,28,26,106,107,108,102,207,210))
> and (det.id_centro = ''2'')
> and (at.fecha_hora_atencion between ''2006-03-31''
> and ''2006-05-31
> 23:59:59'')) as pd_0,
> (select count(pre.id)
> from detalle_movimiento as det
> inner join prescripcion as pre on pre.id = det.id_prescripcion
> and pre.id_centro = det.id_centro
> inner join movimiento as m on det.id_movimiento = m.id
> and det.id_centro = m.id_centro
> inner join receta as r on pre.id_receta = r.id and pre.id_centro =
> r.id_centro
> inner join atencion as at on r.id_atencion = at.id and r.id_centro
> = at.id_centro
> inner join paciente as pac on at.rut_paciente = pac.rut_persona
> and at.id_centro = pac.id_centro
> inner join persona as pers on pac.rut_persona = pers.rut
> and pac.id_centro = pers.id_centro
> where (pre.cantidad > pre.cantidad_despachada AND
>
> pre.cantidad_despachada <> 0)
> and ( m.id_centro_costo in(11,12,15,13,20,29,23,31,24,28,26,106,107
> ,108,102,207,210))
> and (det.id_centro = ''2'')
> and (at.fecha_hora_atencion between ''2006-05-31''
> and ''2006-05-31
> 23:59:59'')) as ppd_0,
> (select count(pre.id)
> from detalle_movimiento as det
> inner join prescripcion as pre on pre.id = det.id_prescripcion
> and pre.id_centro = det.id_centro
> inner join movimiento as m on det.id_movimiento = m.id
> and det.id_centro = m.id_centro
> inner join receta as r on pre.id_receta = r.id and pre.id_centro =
> r.id_centro
> inner join atencion as at on r.id_atencion = at.id and r.id_centro
> = at.id_centro
> inner join paciente as pac on at.rut_paciente = pac.rut_persona
> and at.id_centro = pac.id_centro
> inner join persona as pers on pac.rut_persona = pers.rut
> and pac.id_centro = pers.id_centro
> where (pre.cantidad_despachada = 0)
> and (m.id_centro_costo in(11,12,15,13,20,29,23,31,24,28,26,106,107
> ,108,102,207,210))
> and (det.id_centro = ''2'')
> and (at.fecha_hora_atencion between ''2006-05-31''
> and ''2006-05-31
> 23:59:59'')) as pnd_0
> from
> centro_costo as cc where id in (12, 144, 167, 180, 185)
> LOOP
> RETURN NEXT query2;
> END LOOP;
> return;
> END;'LANGUAGE 'plpgsql';
>
> esa es mi funcion, la syntaxis esta bien por lo menos no da error, pero
> como dije al ejecutar sale:
>
> certamen2bd=# select * from contador() as (centro_costo char,pd_0
> int,pdd_0 int,
> pnd_0 int);
> ERROR: wrong record type supplied in RETURN NEXT
> CONTEXT: PL/pgSQL function "contador" line 75 at return next
>
> realmente no se k es, trate modificando las columnas k creo para guardar
> los valores pero nada, espero k me puedan ayudar.
>

Hola fijate si los tipos de datos devueltos en tus filas corresponden a
los que pones como (centro_costo char,pd_0 int,pdd_0 int,
pnd_0 int);
--
¿Quieres ser parte del 5% o del 95%?
http://www.yor.net/rcaso
Raulinho
97033825-4327239