From: | "jose arellano" <josearellanov(at)gmail(dot)com> |
---|---|
To: | "ayuda postgres" <pgsql-es-ayuda(at)postgresql(dot)org> |
Subject: | Ayuda con funcion record |
Date: | 2006-06-10 17:37:55 |
Message-ID: | 35b7f3e20606101037y1f1fa61qa21bc84750512fc5@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-es-ayuda |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Raul Caso | 2006-06-10 18:28:08 | Re: Ayuda con funcion record |
Previous Message | Martin | 2006-06-10 16:47:21 | Re: RE: [pgsql-es-ayuda] ParĂ¡metros por defecto |