CODEWITHSUNDEEP
sqldatabasepostgresqlplpgsql
Carlos Zhang
Carlos Zhang

Reputation: 67

RETURN cannot have a parameter in function with OUT parameters

I did the db migration from oracle to pgsql and got the code like below:

CREATE OR REPLACE FUNCTION PKG_UTIL_BD_LOGISTICS_getsignerinfo (
  i_opCode T_MQ_LOGIC_TRACK_HEAD_LOG.LP_CODE%TYPE, i_remark T_MQ_LOGIC_TRACK_HEAD_LOG.REMARK%TYPE, i_acceptTime T_MQ_LOGIC_TRACK_HEAD_LOG.SIGNED_TIME%TYPE, i_signer T_MQ_LOGIC_TRACK_HEAD_LOG.SIGNER%TYPE, i_lpcode T_MQ_LOGIC_TRACK_HEAD_LOG.LP_CODE%TYPE,
  o_signer OUT T_MQ_LOGIC_TRACK_HEAD_LOG.SIGNER%TYPE, o_signerTime OUT T_MQ_LOGIC_TRACK_HEAD_LOG.SIGNED_TIME%TYPE, o_status OUT T_MQ_LOGIC_TRACK_HEAD_LOG.STATUS%TYPE ) 
RETURNS RECORD AS $body$
DECLARE

  v_signer        T_MQ_LOGIC_TRACK_HEAD_LOG.SIGNER%TYPE;
  v_signerTime    T_MQ_LOGIC_TRACK_HEAD_LOG.SIGNED_TIME%TYPE;
  v_status        T_MQ_LOGIC_TRACK_HEAD_LOG.STATUS%TYPE;

BEGIN
IF i_lpcode = 'SF' THEN
   IF i_opCode = '8000' THEN
      IF POSITION('：back' in i_remark) > 0 THEN
        v_status := '3';
      ELSE
        v_status := '7';
        v_signerTime := i_acceptTime;
        v_signer := SUBSTR(i_remark, POSITION('：' in i_remark) + 1);
      END IF;
  ELSIF i_opCode = '9999' THEN
      v_status := '3';
    ELSIF i_opCode = '80' THEN
      v_status := '7';
      v_signerTime := i_acceptTime;
    ELSIF i_opCode = 70 THEN
      v_status := i_opCode;
    ELSE
      v_status := '1';
    END IF;
ELSE
  IF i_opCode = 'signed' THEN
      v_signerTime := i_acceptTime;
      v_signer := i_signer;
      v_status:='7';
  ELSE
    v_status:='1';
  END IF;

END IF;
  o_status := v_status;
  o_signer := v_signer;
  o_signerTime := v_signerTime;
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    RAISE EXCEPTION '%', 'PKG_UTIL_BD_LOGISTICS.getSignerInfo fetch parameters' || i_remark || 'value error:' || SQLERRM;
END;

$body$
LANGUAGE PLPGSQL
SECURITY DEFINER

When I executed the code, I got the error below:

ERROR: RETURN cannot have a parameter in function with OUT parameters

Can someone help? I am new to pgsql.

Upvotes: 1

Views: 9449

Answers (3)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

I got the same error below:

ERROR: RETURN cannot have a parameter in function with OUT parameters

Because I returned 2 with RETURN 2; when I used an OUT parameter in a PL/pgSQL function as shown below:

CREATE FUNCTION my_func(OUT value INTEGER) AS $$
BEGIN                 -- ↑ Here
  RETURN 2; -- Here
END;
$$ LANGUAGE plpgsql;

So, I returned nothing with or without RETURN; as shown below, then the error was solved:

CREATE FUNCTION my_func(OUT value INTEGER) AS $$
BEGIN                 -- ↑ Here
  RETURN; -- Here
END;
$$ LANGUAGE plpgsql;

Or:

CREATE FUNCTION my_func(OUT value INTEGER) AS $$
BEGIN                 -- ↑ Here
  -- RETURN; -- Here
END;
$$ LANGUAGE plpgsql;

Upvotes: 0

codechefvaibhavkashyap
codechefvaibhavkashyap

Reputation: 1015

Consider the following example :

CREATE OR REPLACE FUNCTION generate_taskcode(IN classIdVar character varying,IN appIdInt integer, OUT pagecoderet character varying) RETURNS character varying AS $$
    DECLARE
        updateCode character varying;
    BEGIN
        update mytable set lastcodeused = to_char(cast(lastcodeused as INTEGER)+1, 'FM999999999999999999') where 
        classid = classIdVar and appid= appIdInt 
        RETURNING concat(pageName,lastcodeused) as pageName
        into updateCode;
        
        return updateCode;
    END;
$$ LANGUAGE plpgsql

notice the "updatecode" in between begin and end. If you'll try to execute this you'll get the same error as you've mentioned as you can't return variable "** return updateCode**" when you've OUT in function parameters. So the correct def would be as follows :

CREATE OR REPLACE FUNCTION generate_taskcode(IN classIdVar character varying,IN appIdInt integer, OUT pagecoderet character varying) RETURNS character varying AS $$
    DECLARE
        updateCode character varying;
    BEGIN
        update mytable set lastcodeused = to_char(cast(lastcodeused as INTEGER)+1, 'FM999999999999999999') where 
        classid = classIdVar and appid= appIdInt 
        RETURNING concat(pageName,lastcodeused) as pageName
        into updateCode;
        pagecoderet =  updateCode;
        return;
    END;
$$ LANGUAGE plpgsql

Upvotes: 0

Pavel Stehule
Pavel Stehule

Reputation: 45940

The result of function with OUT parameters is specified by values of OUT parameters and only by these values. Although syntax of OUT parameters is similar between PostgreSQL and Oracle, a implementation is maximally different.

Oracle uses reference for OUT parameters - so you can write something like:

CREATE FUNCTION foo(a int, OUT b int)
RETURN boolean IS
BEGIN
  b := a;
  RETURN true;
END;

This function returns boolean value and as "side" effect it modifies second parameter passed by reference.

PostgreSQL doesn't support passing parameters by reference. All parameters are passed by value only. When You use OUT parameter, then there is not passed reference, but the returned values is taken from result composite. Result composite is composed only from OUT parameters. There are no space for some any other. So code:

CREATE OR REPLACE FUNCTION foo(a INT, OUT b int)
RETURNS boolean AS $$
BEGIN
  b := a;
  RETURN true;
END; $$ LANGUAGE plpgsql

is invalid, because real result of foo function is scalar int value, what is in contradiction with declared boolean. RETURN true is wrong too, because result is based on OUT parameters only, and then RETURN should be without any expression.

Equivalent translation of function foo from Oracle to Postgres is:

CREATE OR REPLACE FUNCTION foo(a INT, OUT b int, OUT result boolean)
RETURNS record AS $$
BEGIN
  b := a;
  result := true;
  RETURN;
END; $$ LANGUAGE plpgsql

Easy rule - when function has OUT variables in Postgres, then RETURN statement is used only for ending execution - not for returned value specification. This values is based by OUT parameters.

Upvotes: 6

Related Questions