postgresql - Return rows matching elements of input array in plpgsql function -

i create postgresql function following:

create function avg_purchases( in last_names text[] default '{}' )   returns table(last_name text[], avg_purchase_size double precision) $body$ declare   qry text; begin qry := 'select last_name, avg(purchase_size)            purchases           last_name = any($1)           group last_name' return query execute qry using last_names; end; $body$ 

but see 2 problems here:

1. it not clear me array type useful type of input.

2. this returning 0 rows when do:

   select avg_purchases($${'brown','smith','jones'}$$); 

what missing?

this works:

create or replace function avg_purchases(last_names text[] = '{}')   returns table(last_name text, avg_purchase_size float8) $func$    select last_name, avg(purchase_size)::float8      purchases     last_name = any($1)    group  last_name $func$ language sql; 

call:

select * avg_purchases('{foo,bar,baz,"}weird_name''$$"}'); 

or (update - example dollar-quoting):

select * avg_purchases($x${foo,bar,baz,"}weird_name'$$"}$x$); 

• more how quote string literals:
  insert text single quotes in postgresql

• you don't need dynamic sql here.

• while can wrap plpgsql function (which may useful), simple sql function doing job fine.

• you have type mismatches.

  • the result of avg() may numeric hold precise result. cast float8 make work, alias double precision (you can use either). if need perfect precision, use numeric instead.
  • since group last_name want plain text out parameter instead of text[].

variadic

an array useful type of input. if it's easier client can use variadic input parameter allows pass array list of elements:

create or replace function avg_purchases(variadic last_names text[] = '{}')   returns table(last_name text, avg_purchase_size float8) $func$    select last_name, avg(purchase_size)::float8      purchases    join  (select unnest($1)) t(last_name) using (last_name)    group  last_name $func$ language sql 

call:

select * avg_purchases('foo', 'bar', 'baz', '"}weird_name''$$"}'); 

or (with dollar-quoting):

select * avg_purchases('foo', 'bar', 'baz', $y$'"}weird_name'$$"}$y$); 

be aware standard postgres allows maximum of 100 elements. determined @ compile time preset option:

max_function_args (integer)

reports maximum number of function arguments. determined value of func_max_args when building server. default value 100 arguments.

you can still call array notation when prefixed keyword variadic:

select * avg_purchases(variadic '{1,2,3, ... 99,100,101}'); 

for bigger arrays (100+), use unnest() in subquery , join it, tends scale better:

• optimizing postgres query large in