CODEWITHSUNDEEP
postgresqlplpgsql
KellyM
KellyM

Reputation: 2522

pl/pgSQL - Proper data for parameter used in "WHERE IN" clause?

I am attempting to create a PL/pgSQL function that accepts an array/list of values (properties) that will be used for filtering records using an "IN" clause.

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;

However, I am struggling with what type of data type to use in the parameter list. A single "varchar" is not appropriate as there could be many property values. However, whenever I use an array (as in the code above), I received the following message:

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

What is the property data type to use for this purpose. In example, something that would translate to WHERE t.property_id IN ('123','12','1',...)?

Thanks.

Upvotes: 0

Views: 36

Answers (1)

user330315
user330315

Reputation:

The array is the correct data type, you just need to use an operator that works with that: 

WHERE t.property_id = ANY (properties)

Upvotes: 3

Related Questions