我有一个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('{}')..."
但如果数组为空，则不应在查询中表示此条件
我还try 了两种变体:
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；