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

From jonathansfl
Subject Re: postgres subfunction return error
Date
Msg-id 01a001cebbc4$ced17260$6c745720$@com
Whole thread Raw
In response to Re: postgres subfunction return error  (David Johnston <polobo@yahoo.com>)
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;

 

From: David Johnston [via PostgreSQL] [mailto:[hidden email]]
Sent: Friday, September 27, 2013 1:43 PM
To: jonathansfl
Subject: Re: postgres subfunction return error

 

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.


If you reply to this email, your message will be added to the discussion below:

click here.
NAML



View this message in context: RE: postgres subfunction return error
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

pgsql-sql by date:

Previous
From: Larry Rosenman
Date:
Subject: Re: Can I simplify this somehow?
Next
From: Sergey Konoplev
Date:
Subject: Re: Can I simplify this somehow?