Return rows matching elements of input array in plpgsql function

by

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:

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:

More Related Contents:

  1. Unnest multiple arrays in parallel
  2. Loop over array dimension in plpgsql
  3. Unnest array by one level
  4. Iterating over integer[] in PL/pgSQL
  5. Can PostgreSQL index array columns?
  6. Return multiple columns of the same row as JSON array of objects
  7. Can PostgreSQL have a uniqueness constraint on array elements?
  8. Selecting data into a Postgres array
  9. Compare arrays for equality, ignoring order of elements
  10. Using jsonb_set() for updating specific jsonb array value
  11. Multiple array_agg() calls in a single query
  12. Return SETOF rows from PostgreSQL function
  13. Select from a table variable
  14. jsonb vs jsonb[] for multiple addresses for a customer
  15. how to check if there is any string from index 0 to 5 in an array of type Any [closed]
  16. remove an index number from array
  17. PostgreSQL: running count of rows for a query ‘by minute’
  18. Passing arrays as parameters in bash
  19. How do you efficiently generate a list of K non-repeating integers between 0 and an upper bound N [duplicate]
  20. Store query result in a variable using in PL/pgSQL
  21. Find cosine similarity between two arrays
  22. How to split a string by new lines in Swift
  23. Function to loop through and select data from multiple tables
  24. SQL Sub queries in check constraint
  25. Class-validator – validate array of objects
  26. Creating partial unique index with sqlalchemy on Postgres
  27. Extending Array to check if it is sorted in Swift?
  28. How can I store function pointers in an array? [duplicate]
  29. Is there any way to allocate a standard Rust array directly on the heap, skipping the stack entirely?
  30. Swift: Binary search for standard array?

Leave a Comment