Re: postgres subfunction return error - Mailing list pgsql-sql

Hurray, that works!

Many thanks David

CREATE OR REPLACE FUNCTION custom.pr_test_parentfunction (

  v_action varchar,

  out swv_refcur refcursor,

  out swv_refcur2 refcursor,

  out swv_refcur3 refcursor

)

RETURNS record AS

$body$

DECLARE

   SWV_Action VARCHAR(50) DEFAULT Coalesce(v_Action,'1');

   v_outvar1 REFCURSOR; v_outvar2 REFCURSOR; v_outvar3 REFCURSOR;

BEGIN

    SELECT * INTO v_outvar1, v_outvar2, v_outvar3 FROM custom.pr_test_subfunction(SWV_Action);

    swv_refcur   = v_outvar1;

    swv_refcur2  = v_outvar2;

    swv_refcur3  = v_outvar3;

    RETURN;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

jonathansfl wrote

    SELECT * INTO v_outvar1, v_outvar2, v_outvar3 FROM custom.pr_test_subfunction(SWV_Action);
    OPEN swv_refcur  for SELECT v_outvar1;
    OPEN swv_refcur2 for SELECT v_outvar2;
    OPEN swv_refcur3 for SELECT v_outvar3;
    RETURN;

I've never used cursors in this way so my help is more theory but:

The called-function already created the cursors.  In the parent function you should simply be able to pass them through unaltered:

SELECT * INTO v_outvar1, ...;
swv_refcur := v_outvar1;
...
...
RETURN;

You can possible simply the above and toss the temporary variables but that should not impact the semantics.

David J.