如何检查Postgres中的数组是否为空

Question

我有一个Postgres函数：

CREATE OR REPLACE FUNCTION get_stats(
    _start_date timestamp with time zone,
    _stop_date timestamp with time zone,
    id_clients integer[],
    OUT date timestamp with time zone,
    OUT profit,
    OUT cost
)
RETURNS SETOF record
LANGUAGE plpgsql
AS $$
DECLARE
    query varchar := '';
BEGIN
... -- lot of code
IF id_clients IS NOT NULL THEN
    query := query||' AND id = ANY ('||quote_nullable(id_clients)||')';
END IF;
... -- other code
END;
$$;

所以，如果我运行这样的查询：

SELECT * FROM get_stats('2014-07-01 00:00:00Etc/GMT-3'
                      , '2014-08-06 23:59:59Etc/GMT-3', '{}');

生成的查询具有以下条件：

"... AND id = ANY('{}')..."

但是，如果数组为空，则不应在查询中表示此条件。

如何检查客户端数组是否为空？

我也尝试过两种变体：

IF ARRAY_UPPER(id_clients) IS NOT NULL THEN
    query := query||' AND id = ANY ('||quote_nullable(id_clients)||')';
END IF;

和：

IF ARRAY_LENGTH(id_clients) THEN
    query := query||' AND id = ANY ('||quote_nullable(id_clients)||')';
END IF;

在这两种情况下，我得到了一个错误：ARRAY_UPPER(ARRAY_LENGTH) doesn't exists；

Answer 1

array_length()需要两个参数，第二个参数是数组的维数：

array_length(id_clients, 1) > 0

所以：

IF array_length(id_clients, 1) > 0 THEN
    query := query || format(' AND id = ANY(%L))', id_clients);
END IF;

这既排除了空数组，也排除了NULL。

或者在Postgres 9.4或更高版本中使用cardinality()。See added answer by @bronzenose.

但是，如果您将一个查询连接在一起使用EXECUTE运行，则使用USING子句传递值会更明智。示例：

• execute using
• How to use EXECUTE FORMAT ... USING in postgres function

顺便说一句，要显式检查数组是否为空(正如标题所述--但这不是您需要的)，只需将其与空数组进行比较：

id_clients = '{}'

就这样。你得到：

TRUE ..数组为空

NULL ..数组为空。

FALSE ..任何其他情况(数组有元素-即使只是空元素)

Answer 2

如果出于某些原因不希望提供数组的维度，cardinality将返回空数组的0：

从医生那里：

基数(Anyarray)返回数组中的元素总数，如果数组为空，则返回0。

Answer 3

下面的示例接受array values、NULL或empty string作为查询参数。当您尝试将NULL作为查询参数传递并发送空字符串时，某些框架会导致SQL错误。

查询使用json映射检查数组参数是否为空：

SELECT CAST(ARRAY_TO_JSON(ARRAY[:myArrayParameter]) AS VARCHAR) IN ('[null]', '[""]')