Return rows matching elements of input array in plpgsql function

aggregate-functions, arrays, plpgsql, postgresql, postgresql-9.2

Solution

This works:

CREATE OR REPLACE FUNCTION avg_purchases(last_names text[] = '{}')
  RETURNS TABLE(last_name text, avg_purchase_size float8)
  LANGUAGE sql AS
$func$
SELECT last_name, avg(purchase_size)::float8
FROM   purchases
WHERE  last_name = ANY($1)
GROUP  BY last_name
$func$;

Call:

SELECT * FROM avg_purchases('{foo,Bar,baz,"}weird_name''$$"}');

Or (example with dollar-quoting):

SELECT * FROM avg_purchases($x${foo,Bar,baz,"}weird_name'$$"}$x$);

How to quote string literals:

- Insert text with single quotes in PostgreSQL

You don't need dynamic SQL here.

While you can wrap it into a plpgsql function (which may be useful), a simple SQL function is doing the basic job just fine.

You had type mismatches:

The result of `avg()` may be `numeric` to hold a precise result. A cast to `float8` (alias for `double precision`) makes it work. For perfect precision, use `numeric` instead.

The `OUT` parameter `last_name` must be `text` instead of `text[]`.

`VARIADIC`

An array is a useful type of input. If it's easier for your client you can also use a `VARIADIC` input parameter that allows to pass the array as a list of elements:

CREATE OR REPLACE FUNCTION avg_purchases(VARIADIC last_names text[] = '{}')
  RETURNS TABLE(last_name text, avg_purchase_size float8)
  LANGUAGE sql AS
$func$
SELECT last_name, avg(purchase_size)::float8
FROM   purchases
JOIN  (SELECT unnest($1)) t(last_name) USING (last_name)
GROUP  BY 1
$func$;

Call:

SELECT * FROM avg_purchases('foo', 'Bar', 'baz', '"}weird_name''$$"}');

Or (with dollar-quoting):

SELECT * FROM avg_purchases('foo', 'Bar', 'baz', $y$'"}weird_name'$$"}$y$);

Stock Postgres only allows a maximum of 100 elements. This is determined at compile time by the preset option:

`max_function_args (integer)`

Reports the maximum number of function arguments. It is determined by the value of `FUNC_MAX_ARGS` when building the server. The default value is 100 arguments.

You can still call it with array notation when prefixed with the keyword `VARIADIC`:

SELECT * FROM avg_purchases(VARIADIC '{1,2,3, ... 99,100,101}');

For bigger arrays (100+), consider `unnest()` in a subquery and `JOIN` to it, tends to scale better:

- Optimizing a Postgres query with a large IN

Problem

Original source

Related problems

• Insert text with single quotes in PostgreSQL