RETURN不能在OUT参数的功能中使用参数

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

我做了从oracle到pgsql的db迁移,得到了如下代码:

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:RETURN cannot have a parameter in function with OUT parameters.Can someone help?I am new to pgsql.

当我执行代码时,我得到了错误:RETURN在OUT参数的函数中没有参数。有人帮忙吗?我是pgsql的新手。

1 个解决方案

#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.

OUT参数的功能结果由OUT参数的值指定,并且仅由这些值指定。虽然PostgreSQL和Oracle之间的OUT参数语法相似,但实现方式却截然不同。

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

Oracle使用OUT参数的引用 - 所以你可以这样写:

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.

此函数返回布尔值,并且作为“side”效果,它修改通过引用传递的第二个参数。

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:

PostgreSQL不支持通过引用传递参数。所有参数仅按值传递。使用OUT参数时,没有传递引用,但返回的值取自结果复合。结果复合仅由OUT参数组成。任何其他人都没有空间。所以代码:

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.

是无效的,因为foo函数的实际结果是标量int值,与声明的boolean相矛盾。 RETURN true也是错误的,因为结果仅基于OUT参数,然后RETURN应该没有任何表达式。

Equivalent translation of function foo from Oracle to Postgres is:

从Oracle到Postgres的函数foo的等效转换是:

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.

简单规则 - 当函数在Postgres中具有OUT变量时,RETURN语句仅用于结束执行 - 不用于返回值规范。该值基于OUT参数。

#1