From: | jonathansfl <jonathanbrinkman(at)yahoo(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | postgres subfunction return error |
Date: | 2013-09-25 23:11:17 |
Message-ID: | 1380150677632-5772407.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2013-09-25 23:19:27 | Re: postgres subfunction return error |
Previous Message | Luca Ferrari | 2013-09-23 06:45:51 | Re: the value of OLD on an initial row insert |