RETURN cannot have a parameter in function with OUT parameters

Question

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.

Answer 1

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.