使用plpython 自製函数範例

create function pymul(lst_x integer[]) returns integer as$$from functools import reduceimport operatorreturn 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 Functionwith 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 Functionselect category     , pymul(array_agg(val))  from ithelp190724 group by category;+----------+-------+| category | pymul |+----------+-------+| B        |    15 || C        |    24 || A        |    24 |+----------+-------+(3 rows)