4

## 使用plpython 自製函數範例

``````create function pymul(lst_x integer[])
returns integer as
\$\$
from functools import reduce
import operator
return reduce(operator.mul, lst_x, 1)
\$\$ LANGUAGE plpython3u;

create table ithelp190724 (
id INT GENERATED BY DEFAULT AS IDENTITY
, category char(1) not null
, kind char(1) not null
, val int not null
);

insert into ithelp190724 (category, kind, val) values
('A', 'X', 2), ('A', 'Y', 3), ('A', 'Z', 4),
('B', 'X', 3), ('B', 'Y', 5),
('C', 'X', 4), ('C', 'Y', 2), ('C', 'S', 1), ('C', 'T', 3);

-- as Window Function

with t1 as (
select id
, category
, kind
, array_agg(val) over (partition by category order by kind
rows between unbounded preceding
and current row) as arragg
from ithelp190724
)
select *
, pymul(arragg)
from t1
;

+----+----------+------+-----------+-------+
| id | category | kind |  arragg   | pymul |
+----+----------+------+-----------+-------+
|  1 | A        | X    | {2}       |     2 |
|  2 | A        | Y    | {2,3}     |     6 |
|  3 | A        | Z    | {2,3,4}   |    24 |
|  4 | B        | X    | {3}       |     3 |
|  5 | B        | Y    | {3,5}     |    15 |
|  8 | C        | S    | {1}       |     1 |
|  9 | C        | T    | {1,3}     |     3 |
|  6 | C        | X    | {1,3,4}   |    12 |
|  7 | C        | Y    | {1,3,4,2} |    24 |
+----+----------+------+-----------+-------+
(9 rows)

-- as Aggregate Function

select category
, pymul(array_agg(val))
from ithelp190724
group by category;

+----------+-------+
| category | pymul |
+----------+-------+
| B        |    15 |
| C        |    24 |
| A        |    24 |
+----------+-------+
(3 rows)

``````