arrays - 具有 IN 条件的存储过程语法

Question

(1)

=>CREATE TABLE T1(id BIGSERIAL PRIMARY KEY, name TEXT);
CREATE TABLE

(2)

=>INSERT INTO T1
(name) VALUES
('Robert'),
('Simone');
INSERT 0 2

(3)

SELECT * FROM T1;
 id |  name  
----+--------
  1 | Robert
  2 | Simone
(2 rows)

(4)

CREATE OR REPLACE FUNCTION test_me(id_list BIGINT[]) 
RETURNS BOOLEAN AS
$$
BEGIN
  PERFORM * FROM T1 WHERE id IN ($1);
  IF FOUND THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END;
$$
  LANGUAGE 'plpgsql';
CREATE FUNCTION

我的问题是调用程序时。我无法在网上找到展示如何传递 BIGINT 类型(或整数等)值列表的示例。

我尝试了以下但没有成功(语法错误):

第一种语法:

eway=> SELECT * FROM test_me('{1,2}'::BIGINT[]);
ERROR:  operator does not exist: bigint = bigint[]
LINE 1: SELECT * FROM T1 WHERE id IN ($1)
                                  ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY:  SELECT * FROM T1 WHERE id IN ($1)
CONTEXT:  PL/pgSQL function test_me(bigint[]) line 3 at PERFORM

第二种语法:

eway=> SELECT * FROM test_me('{1,2}');
ERROR:  operator does not exist: bigint = bigint[]
LINE 1: SELECT * FROM T1 WHERE id IN ($1)
                                  ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY:  SELECT * FROM T1 WHERE id IN ($1)
CONTEXT:  PL/pgSQL function test_me(bigint[]) line 3 at PERFORM

第三种语法:

eway=> SELECT * FROM test_me(ARRAY [1,2]);
ERROR:  operator does not exist: bigint = bigint[]
LINE 1: SELECT * FROM T1 WHERE id IN ($1)
                                  ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY:  SELECT * FROM T1 WHERE id IN ($1)
CONTEXT:  PL/pgSQL function test_me(bigint[]) line 3 at PERFORM

关于有效语法的任何线索？

这就像解析器试图在 PEFORM REQUEST 中将 BIGINT 转换为 BIGINT[] 但它对我来说没有任何意义......

Answer 1

传递数组的所有语法变体都是正确的。

• Pass array literal to PostgreSQL function

问题出在函数内部的表达式上。您可以使用 ANY construct 进行测试喜欢@Mureinik provided或许多其他语法变体。在任何情况下都使用 EXISTS 表达式运行测试:

CREATE OR REPLACE FUNCTION test_me(id_list bigint[]) 
  RETURNS bool AS
$func$
BEGIN
   IF EXISTS (SELECT 1 FROM t1 WHERE id = ANY ($1)) THEN
      RETURN true;
   ELSE
      RETURN false;
   END IF;
END
$func$ LANGUAGE plpgsql STABLE;

注意事项

• EXISTS 是最短和最有效的:

  • PL/pgSQL checking if a row exists - SELECT INTO boolean

• 应用于数组的 ANY 构造仅对小型数组有效。对于更长的数组，其他语法变体更快。喜欢:

  IF EXISTS (SELECT 1 FROM unnest($1) id JOIN t1 USING (id)) THEN ...
  

  • How to do WHERE x IN (val1, val2,…) in plpgsql

• 不要引用语言名称，它是一个标识符，而不是一个字符串:LANGUAGE plpgsql

简单变体

当您返回一个 boolean 值时，它可以更简单。它可能只是为了演示，但作为概念证明:

CREATE OR REPLACE FUNCTION test_me(id_list bigint[]) 
  RETURNS bool AS
$func$
SELECT EXISTS (SELECT 1 FROM t1 WHERE id = ANY ($1))
$func$ LANGUAGE sql STABLE;

同样的结果。