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

[Hugo <htakada@xxxxxxxxx>]

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@xxxxxxxxxxx> 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);