postgresql - pl/pgSQL - "WHERE IN"子句中使用的参数的正确数据？

Question

我正在尝试创建一个 PL/pgSQL 函数，它接受将用于使用“IN”子句过滤记录的值(属性)数组/列表。

CREATE OR REPLACE FUNCTION ticket.property_report(start_date DATE, end_date DATE, properties VARCHAR[]) RETURNS 
TABLE(total_labor_mins numeric(10,2), total_parts_cost numeric(10,2), ticket_count bigint, property VARCHAR(6)) AS $$ 
BEGIN
    RETURN QUERY SELECT SUM(c.total_labor_mins), SUM(c.total_parts_cost), COUNT(*) as ticket_count, t.property_id as property
      FROM ticket.ticket AS t LEFT JOIN ticket.ticket_cost_row AS c ON t.id = c.ticket_id 
      WHERE t.property_id IN (properties) AND t.open_date >= start_date AND t.open_date <= end_date
      GROUP BY t.property_id;

END;
$$ LANGUAGE plpgsql;

但是，我正在为在参数列表中使用什么类型的数据类型而苦恼。单个“varchar”是不合适的，因为可能有很多属性值。但是，每当我使用数组(如上面的代码)时，我都会收到以下消息:

SELECT ticket.property_report(TO_DATE('2019-01-01', 'yyyy-MM-dd'), TO_DATE('2019-12-31', 'yyyy-MM-dd'), '{"5305"}');
ERROR:  operator does not exist: character varying = character varying[]
LINE 3:       WHERE t.property_id IN (properties) AND t.open_date >=...
                                  ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY:  SELECT SUM(c.total_labor_mins), SUM(c.total_parts_cost), COUNT(*) as ticket_count, t.property_id as property
      FROM ticket.ticket AS t LEFT JOIN ticket.ticket_cost_row AS c ON t.id = c.ticket_id
      WHERE t.property_id IN (properties) AND t.open_date >= start_date AND t.open_date <= end_date
      GROUP BY t.property_id
CONTEXT:  PL/pgSQL function ticket.property_report(date,date,character varying[]) line 3 at RETURN QUERY

用于此目的的属性数据类型是什么。例如，将转换为 WHERE t.property_id IN ('123','12','1',...)?

谢谢。

Answer 1

数组是正确的数据类型，您只需要使用 operator适用于:

WHERE t.property_id = ANY (properties)