PG里面能创建成功并正常运行的函数在opengauss里面创建报错 ERROR: It is forbidden to use placeholder and dollar quoting together.

我来答

QIU飞

2021-04-15

postgresql opengauss

PG里面能创建成功并正常运行的函数在opengauss里面创建报错 ERROR: It is forbidden to use placeholder and dollar quoting together.

大佬们有没遇到这个问题呀？

图片.png

代码如下：

CREATE OR REPLACE FUNCTION substring_index(varchar, varchar, integer)
RETURNS varchar AS $$
  DECLARE
    tokens varchar[];
    length integer ;
    indexnum integer;
  BEGIN
    tokens := pg_catalog.string_to_array($1, $2);
    length := pg_catalog.array_upper(tokens, 1);
    indexnum := length - ($3 * -1) + 1;
    IF $3 >= 0 THEN
      RETURN pg_catalog.array_to_string(tokens[1:$3], $2);
    ELSE
      RETURN pg_catalog.array_to_string(tokens[indexnum:length], $2);
    END IF;
  END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
复制

我来答

添加附件

问题补充

2条回答

默认

最新

伊织鸟

遇见了一样的问题，怀疑是$符号的问题，改成以下试试，期待你的回复

CREATE OR REPLACE FUNCTION substring_index(varchar, varchar, integer)
RETURNS varchar AS $$
DECLARE
tokens varchar[];
length integer ;
indexnum integer;
BEGIN
tokens := pg_catalog.string_to_array($1, $2);
length := pg_catalog.array_upper(tokens, 1);
indexnum := length - ($3 * -1) + 1;
IF $3 >= 0 THEN
RETURN pg_catalog.array_to_string(tokens[1:$3], $2);
ELSE
RETURN pg_catalog.array_to_string(tokens[indexnum:length], $2);
END IF;
END;
$$ language 'sql' ;复制

有用 0

打赏 0

锁钥

如下修改，给中括号的两个参数添加空格，怀疑是两个参数没识别出来当成一个占位符导致语法报错：tokens[indexnum : length]

CREATE OR REPLACE FUNCTION substring_index(varchar, varchar, integer)
RETURNS varchar AS $$
  DECLARE
    tokens varchar[];
    length integer ;
    indexnum integer;
  BEGIN
    tokens := pg_catalog.string_to_array($1, $2);
    length := pg_catalog.array_upper(tokens, 1);
    indexnum := length - ($3 * -1) + 1;
    IF $3 >= 0 THEN
      RETURN pg_catalog.array_to_string(tokens[1:$3], $2);
    ELSE
      RETURN pg_catalog.array_to_string(tokens[indexnum : length], $2);
    END IF;
  END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
复制