pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCall Function from within Function - parent / dispatch
2+ messages / 2 participants
[nested] [flat]
* Call Function from within Function - parent / dispatch
@ 2018-08-23 13:52 Stefan Zauchenberger <stefan@ice-sys.com>
0 siblings, 1 reply; 2+ messages in thread
From: Stefan Zauchenberger @ 2018-08-23 13:52 UTC (permalink / raw)
To: pgsql-admin
I am having a problem creating a parent function that calls different functions within based on conditions. The functions within all take the exact same arguments and return the same set type. This must be a simple mistake on my part, but not sure of the fix.
When I try to use a RETURN statement, the following error is given:
ERROR: RETURN cannot have a parameter in function returning set
LINE 15: RETURN myfunctionB(paccntid,pitemid,pthing)...
^
HINT: Use RETURN NEXT or RETURN QUERY.
********** Error **********
ERROR: RETURN cannot have a parameter in function returning set
SQL state: 42804
Hint: Use RETURN NEXT or RETURN QUERY.
Character: 288
So, if I try using RETURN NEXT or the PERFORM statement, the client app says:
ERROR: query "SELECT myfunctionB(paccntid,pitemid,pthing)" returned more than one row
CONTEXT: PL/pgSQL function mydispatch(integer, integer, text) at RETURN NEXT
The individual functions within work fine when called on their own. I just need to fire of one or another based on simple logic.
Here is the basic function:
-- Function: mydispatch(integer, integer, text)
-- DROP FUNCTION mydispatch(integer, integer, text)
CREATE OR REPLACE FUNCTION mydispatch(
paccntid integer,
pitemid integer,
pthing text)
RETURNS SETOF myset AS
$BODY$
BEGIN
IF (condition = true)
RETURN myfunctionA(paccntid,pitemid,pthing);
END IF;
RETURN myfunctionB(paccntid,pitemid,pthing);
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION myspatch(integer, integer, text)
OWNER TO admin;
Stefan
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Call Function from within Function - parent / dispatch
@ 2018-08-23 14:22 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stefan Zauchenberger <stefan@ice-sys.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2018-08-23 14:22 UTC (permalink / raw)
To: Stefan Zauchenberger <stefan@ice-sys.com>; +Cc: pgsql-admin
Stefan Zauchenberger <stefan@ice-sys.com> writes:
> I am having a problem creating a parent function that calls different functions within based on conditions. The functions within all take the exact same arguments and return the same set type. This must be a simple mistake on my part, but not sure of the fix.
If you're trying to pass a set result back up, the easiest way is to
use "RETURN QUERY SELECT * FROM child_function(...)". Plain "RETURN"
only works for scalar results, and RETURN NEXT is for producing a
result-set one row at a time.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2018-08-23 14:22 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-08-23 13:52 Call Function from within Function - parent / dispatch Stefan Zauchenberger <stefan@ice-sys.com>
2018-08-23 14:22 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox