PostgreSQL:重用适用于虚拟表数据的函数代码

Question

我的代码适用于 3 列(id | timestamp | value)的数据集(= 多行)并生成单个数值结果(分数) .

现在我想把这段代码放在一个函数中，以便我可以重用它。假设我有 4 个其他查询，它们都生成一个包含这 3 个列的数据集，我想从中计算分数，而不必复制/粘贴我的代码...我该怎么做？似乎无法将 TABLE(...) 类型作为函数参数传递。

我认为我不能使用聚合函数，因为我的评分代码会在内部添加新行、对它们进行分组等等。我需要同时处理所有行并且没有状态转换。

Answer 1

这个问题很有趣，因为它引发了对自定义聚合函数的非标准使用的反射(reflection)。

(...) imagine some query that sorts the rows by id first, then multiplies the very first row.value with the value of 4th, and sets that as new value of the first row. then it returns the first value that is <5 as a score.

我们需要:

• 作为聚合的自定义复合类型对特定类型的值进行操作，
• 状态转换函数，将连续的行保存在临时表中，
• 一个最终函数，执行计算并返回最终结果。

---

create type scores as (id int, val numeric);

create function save_scores(p scores, n scores)
returns scores language plpgsql as $$
begin
    if p is null then 
        create temp table temp_scores of scores;
    end if;
    insert into temp_scores values (n.id, n.val);
    return n;
end $$;

create function calculate_scores(t scores)
returns numeric language plpgsql as $$
declare
    id1 int;
    val4 numeric;
    res numeric;
begin
    select id into id1 from temp_scores order by id limit 1;
    select val into val4 from temp_scores order by id offset 3 limit 1;
    update temp_scores set val = val* val4 where id = id1;
    select val into res from temp_scores where val < 5 order by id limit 1;
    drop table temp_scores;
    return res;
end $$;

聚合:

create aggregate get_scores(scores) (
    sfunc = save_scores,
    finalfunc = calculate_scores,
    stype = scores
);

和一些检查:

select get_scores(row)
from (
    values (4, 3.1), (3, 1.2), (2, 5.2), (1, 2)
    ) row
-- gives 1.2

select get_scores(row)
from (
    values (4, 3.1), (3, 1.2), (2, 5.2), (1, 1)
    ) row
-- gives 3.1

由于使用了临时表，聚合只能在单个查询中使用一次。为简单起见，省略了 timestamp 列。