Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux