Thread: postgres subfunction return error

greetings. I'm trying to write a function that acts like a switchboard,
calling other functions depending on incoming parameters.
I'm getting error: query has no destination for result data
Please advise what we're doing wrong. The subfunctions return a series of
refcursors in a single table and single row (see pr_test_subfunction).
When i run the parent function, i want to see the same results as if i had
run the subfunction - the results of the subfunction should pass-thru to the
parent calling function. Can this be done?

Subfunction:
[CODE]
CREATE OR REPLACE FUNCTION dev.pr_test_subfunction ( v_action varchar, out swv_refcur refcursor, out swv_refcur2
refcursor,out swv_refcur3 refcursor, out swv_refcur4 refcursor, out swv_refcur5 refcursor
 
)
RETURNS record AS
$body$
DECLARE  SWV_Action VARCHAR(50) DEFAULT Coalesce(v_Action,'');  
BEGIN   OPEN SWV_RefCur  for SELECT 1;   OPEN swv_refcur2 for SELECT 2;   OPEN swv_refcur3 for SELECT 3;   OPEN
swv_refcur4for SELECT 4;   OPEN swv_refcur5 for SELECT 5;   RETURN;
 
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
[/CODE]

Parent Function:
[CODE]
CREATE OR REPLACE FUNCTION dev.pr_test_parentfunction ( v_action varchar, out swv_refcur refcursor, out swv_refcur2
refcursor,out swv_refcur3 refcursor, out swv_refcur4 refcursor, out swv_refcur5 refcursor
 
)
RETURNS record AS
$body$
DECLARE  SWV_Action VARCHAR(50) DEFAULT Coalesce(v_Action,'1');  
BEGIN      IF SWV_Action = '1' THEN       SELECT * FROM dev.pr_test_subfunction(SWV_Action);    ELSIF SWV_Action = '2'
THEN      SELECT * FROM dev.pr_test_subfunction(SWV_Action);    ELSE       OPEN SWV_RefCur  for SELECT 1;       OPEN
swv_refcur2for SELECT 2;           END IF;
 
   RETURN;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
[/CODE]




--
View this message in context: http://postgresql.1045698.n5.nabble.com/postgres-subfunction-return-error-tp5772407.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

jonathansfl wrote
> greetings. I'm trying to write a function that acts like a switchboard,
> calling other functions depending on incoming parameters.
> I'm getting error: query has no destination for result data
> 
> 
>         SELECT * FROM dev.pr_test_subfunction(SWV_Action); 

In pl/pgsql if you do not use an "INTO" clause on a select statement you
must replace the "SELECT" with "PERFORM".  Failing to do so results in the
error you are seeing.  In this case your calls to the sub-function do not
magically populate the parent function variables.  You must manually map the
output of the sub-function call query onto the parent variables.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/postgres-subfunction-return-error-tp5772407p5772408.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

I'm trying to pass a REFCURSOR variable from a subfunction to its parent
calling function, who will then pass it to the user (for parsing).
thanks to David J I fixed it somewhat, but the user now receives the TEXT of
<unnamed portal 32> (etc.) instead of the actual data in that REFCURSOR
variable.

I think the problem is with the "OPEN swv_refcur for SELECT v_outvar;" which
is not returning the REFCURSOR's actual data.
thank you for your help!!
Jonathan

NEW PARENT FUNCTION CODE:
[CODE]
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);   OPEN swv_refcur  for SELECT v_outvar1;   OPEN swv_refcur2 for SELECT
v_outvar2;  OPEN swv_refcur3 for SELECT v_outvar3;   RETURN;
 
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
[/CODE]



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/postgres-subfunction-return-error-tp5772407p5772613.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

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.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/postgres-subfunction-return-error-tp5772407p5772627.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.