Ayuda con funcion record

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.

Responses

Browse pgsql-es-ayuda by date

  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