Re: how to return a result set from a stored procedure

hi Petar, thanks for your answer, I tried it and got this message:

ERROR: SELECT query has no destination for result data
HINT: If you want to discard the results, use PERFORM instead.
CONTEXT: PL/pgSQL function "fn_get_total_remitidoxprovisio n1" line 5 at SQL
statement

could you advice me on this ??

thanks

Hugo

On 11/06/05, Petar Jovanovic <jpetar(at)pinsoft(dot)com> wrote:
>
> Hugo wrote:
>
> > Hi everybody
> >
> > I am trying to write a stored procedure that returns a result set but
> > it is not working
> > this is the function:
> > ///
> > CREATE OR REPLACE FUNCTION
> > remisiones.fn_get_total_remitidoxprovision1("numeric")
> > RETURNS SETOF record AS
> > $BODY$
> > begin
> > select rm.provision as provision,
> > drm.producto as producto,
> > sum(drm.cantidad) as cantidad
> > FROM remisiones.remisiones rm, remisiones.detalles_remision drm
> > WHERE rm.remision = drm.remision and rm.provision = $1
> > GROUP BY rm.provision, drm.producto
> > ORDER BY rm.provision, drm.producto;
> > end;$BODY$
> >
> > ///
> > If I call this function from the interactive sql of pgadminIII I get
> > this result:
> > select * from fn_gert_total_remitidosxprovision(1)
> >
> ---------------------------------------------------------------------------
> > row refcursor
> > 1 <unnamed porta1>
> >
> >
> > is there a way to display the value of the rows returned, i need it
> > becouse I need to use it in a Datawindow definition in an Powerbuilder
> > app.
> >
> > thanks in advance
> >
> > Hugo
> >
> >
> In PL/pgSQL you have to specify structure or records that function will
> return. For example :
> SELECT * FROM fn_get_total_remitidoxprovision1(1) AS (provision integer,
> producto integer,cantidad float);
>
>