postgresql - 在 PostgreSQL 中使用 SELECT 设置不工作

Question

我试图在 PostgreSQL 中使用 SET 和 SELECT，但似乎出现错误:

CREATE FUNCTION  "GET_READ_AUTHORITY_BITS_3" (RoleBO TEXT)
RETURNS TEXT
AS
$BODY$
DECLARE 
  DECLARE READ_AUTHORITY_BITS TEXT;
BEGIN
    SET READ_AUTHORITY_BITS = SELECT OVERLAY('Apple' placing '' from 1 for 1);
END
$BODY$
LANGUAGE plpgsql VOLATILE

错误:

ERROR: syntax error at or near "SELECT" LINE 8: SET READ_AUTHORITY_BITS = SELECT OVERLAY('Apple' placing... ^ SQL state: 42601 Character: 164

但是 SET 和 SELECT 分别工作正常:

CREATE FUNCTION  "GET_READ_AUTHORITY_BITS_2" (RoleBO TEXT)
RETURNS TEXT
AS
$BODY$
DECLARE 
  DECLARE READ_AUTHORITY_BITS TEXT;
BEGIN
    SET READ_AUTHORITY_BITS = 'a';
    SELECT OVERLAY('Apple' placing '' from 1 for 1);
END
$BODY$
LANGUAGE plpgsql VOLATILE

我是不是漏掉了什么？

Answer 1

SET 不用于设置 plpgsql 函数变量。它用于设置运行时配置参数。查看documentation .

如果您想将 OVERLAY 函数的结果赋值给变量，您可以通过多种方式实现:

CREATE FUNCTION  "GET_READ_AUTHORITY_BITS_3" (RoleBO TEXT)
RETURNS TEXT
AS
$BODY$
DECLARE 
  READ_AUTHORITY_BITS TEXT;
BEGIN
    -- 1.
    READ_AUTHORITY_BITS := OVERLAY('Apple' placing '' from 1 for 1);
    -- 2.
    READ_AUTHORITY_BITS := (SELECT OVERLAY('Apple' placing '' from 1 for 1));
    -- 3.
    SELECT OVERLAY('Apple' placing '' from 1 for 1)
    INTO READ_AUTHORITY_BITS;
END
$BODY$
LANGUAGE plpgsql VOLATILE