agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedpostgres subfunction return error
6+ messages / 2 participants
[nested] [flat]
* postgres subfunction return error
@ 2013-09-25 23:11 jonathansfl <jonathanbrinkman@yahoo.com>
0 siblings, 2 replies; 6+ messages in thread
From: jonathansfl @ 2013-09-25 23:11 UTC (permalink / raw)
To: pgsql-sql
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 (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: postgres subfunction return error
@ 2013-09-25 23:19 David Johnston <polobo@yahoo.com>
parent: jonathansfl <jonathanbrinkman@yahoo.com>
1 sibling, 1 reply; 6+ messages in thread
From: David Johnston @ 2013-09-25 23:19 UTC (permalink / raw)
To: pgsql-sql
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 (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: postgres subfunction return error
@ 2013-09-25 23:33 jonathansfl <jonathanbrinkman@yahoo.com>
parent: David Johnston <polobo@yahoo.com>
0 siblings, 0 replies; 6+ messages in thread
From: jonathansfl @ 2013-09-25 23:33 UTC (permalink / raw)
To: pgsql-sql
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:ml-node+s1045698n5772408h6@n5.nabble.com]
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
<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsu
bscribe_by_code&node=5772407&code=am9uYXRoYW5icmlua21hbkB5YWhvby5jb218NTc3Mj
QwN3wtNDQ0Mzc4MjI4> .
<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macr
o_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.B
asicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.templ
ate.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-insta
nt_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: postgres subfunction return error
@ 2013-09-27 14:57 jonathansfl <jonathanbrinkman@yahoo.com>
parent: jonathansfl <jonathanbrinkman@yahoo.com>
1 sibling, 1 reply; 6+ messages in thread
From: jonathansfl @ 2013-09-27 14:57 UTC (permalink / raw)
To: pgsql-sql
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: postgres subfunction return error
@ 2013-09-27 17:42 David Johnston <polobo@yahoo.com>
parent: jonathansfl <jonathanbrinkman@yahoo.com>
0 siblings, 1 reply; 6+ messages in thread
From: David Johnston @ 2013-09-27 17:42 UTC (permalink / raw)
To: pgsql-sql
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: postgres subfunction return error
@ 2013-09-27 21:02 jonathansfl <jonathanbrinkman@yahoo.com>
parent: David Johnston <polobo@yahoo.com>
0 siblings, 0 replies; 6+ messages in thread
From: jonathansfl @ 2013-09-27 21:02 UTC (permalink / raw)
To: 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:ml-node+s1045698n5772627h95@n5.nabble.com]
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:
http://postgresql.1045698.n5.nabble.com/postgres-subfunction-return-error-tp
5772407p5772627.html
To unsubscribe from postgres subfunction return error, click here
<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsu
bscribe_by_code&node=5772407&code=am9uYXRoYW5icmlua21hbkB5YWhvby5jb218NTc3Mj
QwN3wtNDQ0Mzc4MjI4> .
<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macr
o_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.B
asicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.templ
ate.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-insta
nt_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
NAML
--
View this message in context: http://postgresql.1045698.n5.nabble.com/postgres-subfunction-return-error-tp5772407p5772658.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2013-09-27 21:02 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-09-25 23:11 postgres subfunction return error jonathansfl <jonathanbrinkman@yahoo.com>
2013-09-25 23:19 ` David Johnston <polobo@yahoo.com>
2013-09-25 23:33 ` jonathansfl <jonathanbrinkman@yahoo.com>
2013-09-27 14:57 ` jonathansfl <jonathanbrinkman@yahoo.com>
2013-09-27 17:42 ` David Johnston <polobo@yahoo.com>
2013-09-27 21:02 ` jonathansfl <jonathanbrinkman@yahoo.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox