CODEWITHSUNDEEP
postgresqlaggregate-functionsplpgsql
ssc
ssc

Reputation: 9903

postgresql: parameterize aggregation type?

I need to provide a number of stored procedures that return the result of an aggregate function run on a selection of values in the database, i.e. average, sum, min, max, etc. The selection of values is always done in a similar manner.

My first, naive implementation consists of one stored proc per aggregate function type, e.g. get_avg(...), get_sum(...). The functions obviously share quite a lot of duplicate code. I wonder if there is a way to move the aggregate type into a parameter or so and then use one stored proc only, e.g. get_aggregate(aggr_type, ...)

The only way I can think of as far as the pl/pgsql implementation is concerned is to select the values that should go into the aggregate and then use that in as many if aggr_type == ... / else clauses as there are aggregate types. This is not very flexible though and requires change of the code whenever a new aggregate type should be supported.

Is there any way to parameterize the name of a function in pl/pgsql ? Can ayone think of an approach that I fail to see ?

Upvotes: 0

Views: 226

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78513

You could, technically, use the execute statement in your plpgsql function:

return query
execute $x$
select id, $x$ || quote_ident(agg_func) || $x$(col)::numeric as agg
from bar
$x$;

(See below for a proper example). The issue is, this will mean parsing/planning your query on every call.

On occasion, a better option will be to create a function that creates the various needed functions. Something like this:

create or replace function generate_agg_functions(_table regclass) returns void as $$
declare
  _agg_type text[] = '{sum, avg}';
  _ret_type regtype[] = '{numeric, numeric}';
  rec record;
begin
  for rec in select _agg_type[i] as agg_type, _ret_type[i] as ret_type
  from generate_subscripts(_agg_type, 1) i
  loop
  execute $x$
  create or replace function $x$ || quote_ident(_table || '_' || rec.agg_func) || $x$()
    returns table (id int, $x$ || quote_ident(rec.agg_type) || ' ' || ret_type || $x$)
  $def$
  begin
    return query
    select id,
           $x$ || quote_ident(rec.agg_type) || $x$(col)::$x$ || rec.ret_type || $x$
             as $x$ || quote_ident(rec.agg_type) || $x$
    from $x$ || _table || $x$;
  end;
  $def$ language plpgsql stable;
  $x$;
  end loop;
end;
$$ language plpgsql;

It's a bit trickier to write, but you'll end up not duplicating code without compromising the performance of the individual aggregates.

Upvotes: 1

Related Questions