[SQL] postgres subfunction return error

2013-09-25 Thread jonathansfl 
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_refcur4 for 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_refcur2 for 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.


-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] postgres subfunction return error

2013-09-25 Thread David Johnston 
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.


-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] postgres subfunction return error

2013-09-25 Thread jonathansfl 
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_refcurfor SELECT v_outvar1;

OPEN swv_refcur2 for SELECT v_outvar2;

OPEN swv_refcur3 for SELECT v_outvar3;

 

 

From: David Johnston [via PostgreSQL]
[mailto:[email protected]] 
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:

http://postgresql.1045698.n5.nabble.com/postgres-subfunction-return-error-tp
5772407p5772408.html 

To unsubscribe from postgres subfunction return error, click here
 .
 

NAML 





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