Thread: postgres subfunction return error

postgres subfunction return error

From
jonathansfl
Date:
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.



Re: postgres subfunction return error

From
David Johnston
Date:
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.



Re: postgres subfunction return error

From
jonathansfl
Date:

Thank you!

What kind of variable would I declare? Is this any form of right?

No change to subfunction.

In PARENT Function:

DECLARE v_outvar1 REFCURSOR; v_outvar2 REFCURSOR; v_outvar3 REFCURSOR;

?And use:

SELECT * INTO v_outvar1, v_outvar2, v_outvar3 FROM dev.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;

 

 

From: David Johnston [via PostgreSQL] [mailto:[hidden email]]
Sent: Wednesday, September 25, 2013 7:19 PM
To: jonathansfl
Subject: Re: postgres subfunction return error

 

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.


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.

Re: postgres subfunction return error

From
jonathansfl
Date:
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.



Re: postgres subfunction return error

From
David Johnston
Date:
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.



Re: postgres subfunction return error

From
jonathansfl
Date:

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.