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@pinsoft.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);