PostgreSQL函数调用报错：operator does not exist: character varying[] = text 求解

我编写了一个PostgreSQL函数report.fn_get_metrics，包含text[]类型的输入参数location_ids：

CREATE OR REPLACE FUNCTION report.fn_get_metrics(
    from_date timestamp without time zone,
    to_date timestamp without time zone,
    location_ids text[])
    RETURNS TABLE(demand_new_count bigint) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000
AS $BODY$
BEGIN
    RETURN QUERY
    WITH 
    cte_pos AS (
    SELECT
        COUNT(DISTINCT job_id) AS demand_new_count
    FROM report.pg_job_data pjd
    WHERE creation_date >= from_date AND creation_date <= to_date
    AND (location_ids IS NULL OR location_id IN (SELECT unnest(location_ids)))
)
    SELECT
        demand_new_count
    FROM cte_pos;
END;
$BODY$;

尝试以下两种方式调用函数时，均报错operator does not exist: character varying[] = text：

1. 直接传递字符串数组：

SELECT * FROM report.fn_get_metrics(
    '2023-01-01'::timestamp, -- from_date
    '2023-06-30'::timestamp, -- to_date
    ARRAY['638a4f2c-11c4-4e15-ae78-d6bf01ef2fad']
);

2. 将数组转换为UUID[]类型：

SELECT * FROM report.fn_get_metrics(
    '2023-01-01'::timestamp, -- from_date
    '2023-06-30'::timestamp, -- to_date
    ARRAY['638a4f2c-11c4-4e15-ae78-d6bf01ef2fad']::UUID[]
);

且无法将函数的输入参数修改为varchar[]或uuid[]类型，求解决方法。

错误根源是函数中unnest(location_ids)返回的text类型，与表report.pg_job_data中location_id字段的实际类型（varchar或uuid）不兼容，导致PostgreSQL找不到对应的比较运算符。由于无法修改函数参数类型，只需在函数内部对数组元素做类型转换即可解决。

情况1：location_id为UUID类型 #

修改函数中CTE的过滤条件，将unnest后的text元素转换为uuid：

CREATE OR REPLACE FUNCTION report.fn_get_metrics(
    from_date timestamp without time zone,
    to_date timestamp without time zone,
    location_ids text[])
    RETURNS TABLE(demand_new_count bigint) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000
AS $BODY$
BEGIN
    RETURN QUERY
    WITH 
    cte_pos AS (
    SELECT
        COUNT(DISTINCT job_id) AS demand_new_count
    FROM report.pg_job_data pjd
    WHERE creation_date >= from_date AND creation_date <= to_date
    AND (location_ids IS NULL OR location_id IN (SELECT unnest(location_ids)::uuid))
)
    SELECT
        demand_new_count
    FROM cte_pos;
END;
$BODY$;

情况2：location_id为VARCHAR类型 #

如果location_id是varchar类型，将unnest后的元素转换为varchar：

CREATE OR REPLACE FUNCTION report.fn_get_metrics(
    from_date timestamp without time zone,
    to_date timestamp without time zone,
    location_ids text[])
    RETURNS TABLE(demand_new_count bigint) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000
AS $BODY$
BEGIN
    RETURN QUERY
    WITH 
    cte_pos AS (
    SELECT
        COUNT(DISTINCT job_id) AS demand_new_count
    FROM report.pg_job_data pjd
    WHERE creation_date >= from_date AND creation_date <= to_date
    AND (location_ids IS NULL OR location_id IN (SELECT unnest(location_ids)::varchar))
)
    SELECT
        demand_new_count
    FROM cte_pos;
END;
$BODY$;

调用方式 #

修改函数后，直接使用最初的调用方式即可，无需转换数组类型：

SELECT * FROM report.fn_get_metrics(
    '2023-01-01'::timestamp,
    '2023-06-30'::timestamp,
    ARRAY['638a4f2c-11c4-4e15-ae78-d6bf01ef2fad']
);

内容的提问来源于stack exchange，提问作者abhi1489