Can a plpython function take multiple rows as parameter?

Question

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.

Your example is not very clear. You want the avg for each array element, right? Please clarify. And, as always, add your version of Postgres. — Erwin Brandstetter, Apr 12 '15 at 22:06
Yes, your interpretation was right, I'll edit the question for further reference. Version of postgres is unimportant, I can use whichever solves the problem best. — TigerShark, Apr 12 '15 at 23:34

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

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:

unnest each array in a LATERAL join (implicit here), remembering the ordinal position of elements
GROUP BY this position.
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:

PostgreSQL unnest() with element number

Very good answer, thx. **WITH ORDINALITY** is the key component I was missing. Seems that searching in google without the exact keywords has become useless nowadays :P — TigerShark, Apr 12 '15 at 23:33

Can a plpython function take multiple rows as parameter?

1 Answers1

Function

SQL