Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fsq7G-0003eH-Cz for pgsql-admin@arkaria.postgresql.org; Thu, 23 Aug 2018 13:57:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fsq7D-0000UW-Rh for pgsql-admin@arkaria.postgresql.org; Thu, 23 Aug 2018 13:57:35 +0000 Received: from makus.postgresql.org ([174.143.35.229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fsq4K-0000O5-Q0 for pgsql-admin@lists.postgresql.org; Thu, 23 Aug 2018 13:54:37 +0000 Received: from smtpauth1.rolet.com ([199.59.142.9]) by makus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fsq2B-0000od-Au for pgsql-admin@postgresql.org; Thu, 23 Aug 2018 13:52:24 +0000 Received: from [192.168.11.150] ([136.32.60.195]) (authenticated bits=0) by smtpauth1.rolet.com (8.13.8/8.13.8) with ESMTP id w7NDqHF6031033 for ; Thu, 23 Aug 2018 08:52:19 -0500 User-Agent: Microsoft-MacOutlook/10.10.0.180812 Date: Thu, 23 Aug 2018 08:52:17 -0500 Subject: Call Function from within Function - parent / dispatch From: Stefan Zauchenberger To: Message-ID: <7B1A245D-F8DD-4AAB-8ACF-3C2C7F5A9563@ice-sys.com> Thread-Topic: Call Function from within Function - parent / dispatch Mime-version: 1.0 Content-type: multipart/alternative; boundary="B_3617859139_1660980860" X-UR-SAUTH-1: ac20-ziceman X-Scanned-By: MIMEDefang 2.67 on 199.59.142.9 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3617859139_1660980860 Content-type: text/plain; charset="UTF-8" Content-transfer-encoding: 7bit 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 --B_3617859139_1660980860 Content-type: text/html; charset="UTF-8" Content-transfer-encoding: quoted-printable

I am having a problem creating a parent function tha= t calls different functions within based on conditions. The functions within= all take the exact same arguments and return the same set type. This must b= e a simple mistake on my part, but not sure of the fix.

When I try to use a RETURN statement, the following error is give= n:

ERROR: RETURN canno= t have a parameter in function returning set
LINE 15: RETURN myfunctionB(= paccntid,pitemid,pthing)...
^
HINT: Use RETURN NEXT or RETURN QUERY.

********** Error **********<= /p>

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 usin= g 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 ind= ividual 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: m= ydispatch(integer, integer, text)

= -- DROP F= UNCTION mydispatch(integer, integer, text)

= CREATE OR REPLACE FUNCTION mydispatch(
paccntid integer,
pitemid integ= er,
pthing text)
RETURNS SETOF myset AS
$BODY$

BEGIN

IF (condition =3D true)

RE= TURN 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

 

 

=
--B_3617859139_1660980860--