Select rows such that names match elements of input array for pgsql function

This works:

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

Call:

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

Or (update - example with dollar-quoting):

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

• More about how to quote string literals:
  Insert varchar 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 job just fine.

• You have type mismatches.

  • the result of avg() may be numeric to hold a precise result. I cast to float8 to make it work, which is just an alias for double precision (you can use either). If you need perfect precision, use numeric instead.
  • Since you GROUP BY last_name you want a plain text OUT parameter 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:

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

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$);

Be aware that standard 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+), I would also use unnest() in a subquery and JOIN to it, which tends to scale better.