Search code examples
arrayspostgresqlaggregate-functionsplpython

Can a plpython function take multiple rows as parameter?

I need to know that to make functions that take multiple rows of arrays and returns a result array.

For example:

Table some_table

id | num_array
1  | [1,1,1]
2  | [2,2,2]

Then running:

SELECT custom_avg(num_array) FROM some_table;

And get a an array with the element-wise operation (avg in this example):

[1.5, 1.5, 1.5]

Something like the standard aggregate functions that operate over rows.

After almost 2 hours of looking at docs and googling I can't find a straight answer to this.

I know the array can be unrolled into a table but I'm trying to avoid that. If this kind of functions can be solved with standard SQL that would be useful too.

Solution

  • Function

    Postgres functions cannot take tables ("multiple rows") as parameter. Per documentation:

    The argument types can be base, composite, or domain types, or can reference the type of a table column.

    You would pass a table by reference using a cursor or a temp table ...

    SQL

    But you can solve most anything with plain SQL. To compute an average for every position in a one-dimensional array column:

    You did not provide your version, so I am assuming the current one. WITH ORDINALITY requires Postgres 9.4:

    SELECT ARRAY (
   SELECT avg(elem) AS avg_elem
   FROM   tbl t
        , unnest (t.num_array) WITH ORDINALITY a(elem, rn)
   GROUP  BY a.rn
   ORDER  BY a.rn
   ) AS arr_avg;

    Basically:

    1. unnest each array in a LATERAL join (implicit here), remembering the ordinal position of elements
    2. GROUP BY this position.
    3. Construct a resulting array, ordered by this position.

    There are less elegant alternatives in older versions. For Postgres 9.3:

    SELECT ARRAY (
   SELECT avg(t.num_array[i]) AS avg_elem
   FROM   tbl t, generate_subscripts(t.num_array, 1) i
   GROUP  BY i
   ORDER  BY i
   ) AS arr_avg;

    SQL Fiddle.

    More: