Return rows matching elements of input array in plpgsql function

Question

I would like to create a PostgreSQL function that does something like the following:

CREATE FUNCTION avg_purchases( IN last_names text[] DEFAULT '{}' )
  RETURNS TABLE(last_name text[], avg_purchase_size double precision)
AS
$BODY$
DECLARE
  qry text;
BEGIN
qry := 'SELECT last_name, AVG(purchase_size) 
          FROM purchases
          WHERE last_name = ANY($1)
          GROUP BY last_name'
RETURN QUERY EXECUTE qry USING last_names;
END;
$BODY$

But I see two problems here:

It is not clear to me that array type is the most useful type of input.

This is currently returning zero rows when I do:

SELECT avg_purchases($${'Brown','Smith','Jones'}$$);

What am I missing?

The use of text[] in the return type was a typo. I leave it here because the accepted answer references that mistake. — Brian B
– Brian B, Commented Jul 31, 2013 at 20:10
It turns out that another big mistake was that the calling syntax (after correcting the problem according to the post from @Erwin) needed to be SELECT avg_purchases($${Brown,Smith,Jones}$$) or SELECT avg_purchases('{Brown,Smith,Jones}') — Brian B
– Brian B, Commented Jul 31, 2013 at 20:13

Erwin Brandstetter · Accepted Answer · 2022-05-29 22:51:06Z

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

Actually these will be big arrays in practice, so I am particularly keen on your unnest() suggestion.

Collectives™ on Stack Overflow

Return rows matching elements of input array in plpgsql function

1 Answer 1

`VARIADIC`

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

VARIADIC

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related

`VARIADIC`