agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: jonathansfl <jonathanbrinkman@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: postgres subfunction return error
Date: Wed, 25 Sep 2013 16:11:17 -0700 (PDT)
Message-ID: <1380150677632-5772407.post@n5.nabble.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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

view thread (6+ messages)  latest in thread

Message-ID: <1380150677632-5772407.post@n5.nabble.com>
Permalink:  ../1380150677632-5772407.post@n5.nabble.com/
Also on:    postgresql.org/message-id/1380150677632-5772407.post@n5.nabble.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: jonathanbrinkman@yahoo.com
  Subject: Re: postgres subfunction return error
  In-Reply-To: <1380150677632-5772407.post@n5.nabble.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox