Postgres C函数-聚合，变分，任何数量的任何类型的参数

Question

我想用C编写一个聚合函数。聚合函数将用作：

select f(rc, col1, col2,..., coln) from table

然后sfunc将具有签名(暂时忽略类型)

f(_state, rc, col1, col2,..., coln)

解释如下：我希望函数返回rc或_state.rc，具体取决于当前的_state。在伪代码中，函数显示如下：

f(_state, rc, col1, col2,..., coln)
{
nargs = extract_variadic_args(fcinfo, 0, false, &args, &types, &nulls);
for (i = 0; i < nargs; i++) {
if (_state.coli > coli) return (_state.rc, _state.col1, ..., _state.colnargs)
else if (_state.coli == coli) continue;
else return (rc, col1, ..., colnargs);
}
return (rc, col1, ..., colnargs);
}

从本质上讲，聚合是一个折叠，它返回与由col1定义的某个阶的最大值。。。，coln。

例如，给定一些表格

T(rc text, col1 int, col2 real, col3 timestamp)

T('Bob', 1, 2.0, 'May 30 2020')
T('Tom', 2, 1.0, 'May 10 2020')
T('Rich', 3, 1.0, 'Apr 01 2020')
T('Bill', 3, 2.0, 'Apr 02 2020')
T('Andy', 3, 2.0, 'Apr 03 2020')
select f(rc, col1, col2,..., coln) res from T
res
------
'Andy'

Andy被退回的原因是它在(col1，…，coln)。

这个函数不能用PGSQL编写(据我所知)，因为任何元素都需要用来指代同一类型的用法。

然而，我相信这可以在C中完成，json_build_object的实现就是明证。看见https://doxygen.postgresql.org/json_8c.html#aeb269f2659c7bdfc734428c2cd211a4e

json_build_object函数接受任意数量的任意类型的参数。

然而，对于我提议的函数f，还有一个额外的复杂性。聚合中sfunc的第一个参数是用于维护fold(stype)状态的类型。stype必须能够容纳任意数量的任意类型的值进行比较到传入的参数(col1，…，coln)。

基本上，苯乙烯类似

CREATE TYPE priority_type AS (
rc  anyelement, 
col1 any 
... 
coln any
);

f定义为匹配

f(_state priority_type, rc anyelement, col1 any, ..., coln any) returns anyelement

基于json_build_object，我相信这是可以做到的，但我不清楚如何处理priority_type，也不清楚如何使用PG_RETURN_DATAUM返回一个类型的值，直到我检查rc参数的类型时我才知道。换句话说，在我看到的所有示例中，PG_RETURN_*似乎知道它需要返回的类型。在某些情况下，可能存在需要根据其他类型(例如rc类型)构造一个值，然后返回构造的值。既然我们不知道我们在运行时之前返回的类型，我不清楚如何在不包括每种可能的rc类型的case的情况下构造和返回该类型。对于我在这里提议的功能，我们可能可以绕过构造新值的需要，因为我们要么返回rc，要么返回_state.rc，但这取决于priority_type最终是如何构建的。

我是postgres C函数的新手，只写过一两次，这似乎是一个非常高级的用例。如果有人能把一个有效的实现放在一起，那将是令人惊叹的。

编辑(基于Laurenz Albe的回答)：问题仍然存在

劳伦斯，非常感谢你的回答。我发现它非常有趣，我花了一些时间来完全理解它，并对它进行扩展，看看它是否可以在我的系统中使用。我在这里为其他可能觉得有用的读者提供了完整的代码。不幸的是，我不能为自己使用它。我相信我仍然需要一个C函数，我会在包含代码后解释确切的原因。顺便说一句，我认为C函数不需要以字符串形式传入的列名。再次调用json_build_object，我们可以看到可变类型列的可变数量不是作为字符串传递的；而且，我认为这是我的情况所需要的。

create table test_t(rc text, col1 int, col2 real, col3 timestamp);
insert into test_t values
('Bob', 1, 2.0, 'May 30 2020'),
('Tom', 2, 1.0, 'May 10 2020'),
('Rich', 3, 1.0, 'Apr 01 2020'),
('Bill', 3, 2.0, 'Apr 02 2020'),
('Andy', 3, 2.0, 'Apr 03 2020');
-- See: https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=c179acbdbc2f0a52f0d5f81a9a9266c7
create or replace function util.map_array(text, anyarray, out anyarray) returns anyarray language plpgsql as $$
begin
--   select
--   util.map_array('abs(#)', array[-1,-3,-2]),
--   'reverse(#)' -< '{abc,def}'::text[],
--   '''t1.''||#' -< '{abc,def}'::text[],
--   'replace(#, ''a'', ''x'')' -< '{aba,bab}'::text[];
execute format('select array(select %s)', replace($1, '#', 'unnest($1)')) into $3 using $2;
return;
end $$;
create or replace function util.map_string(text, text, out text) returns text language plpgsql as $$
begin
--   select
--   'reverse(#)' -< 'abc,def',
--   '''t1.''||#' -< 'abc,def',
--   'replace(#, ''a'', ''x'')' -< 'abc,def';
execute format('select array_to_string(array(select %s), '','')',
replace($1, '#', 'unnest(string_to_array($1,'',''))')) into $3 using $2;
return;
end $$;
create operator -< (procedure = util.map_array, leftarg = text, rightarg = anyarray);
create operator -< (procedure = util.map_string, leftarg = text, rightarg = text);
CREATE or replace FUNCTION util.max_by_cols_withproc(_state anyelement, cr anyelement, proc regprocedure, cols text) RETURNS anyelement
LANGUAGE plpgsql AS
$$
DECLARE
r boolean;
BEGIN
EXECUTE format('SELECT %s($1, $2, $3)', proc::regproc) INTO r
USING _state, cr, cols;
IF NOT r THEN
RETURN _state;
ELSE
RETURN cr;
END IF;
END;
$$;
CREATE or replace FUNCTION util.max_by_cols(_state anyelement, cr anyelement, cols text) RETURNS anyelement
LANGUAGE plpgsql AS
$$
DECLARE
r boolean;
BEGIN
EXECUTE format('SELECT %s($1, $2, $3)', 'util.compare_by_cols'::regproc) INTO r
USING _state, cr, cols;
IF NOT r THEN
RETURN _state;
ELSE
RETURN cr;
END IF;
END;
$$;
CREATE AGGREGATE util.max_by_cols(anyelement, regprocedure, text) (
SFUNC = util.max_by_cols_withproc,
STYPE = anyelement
);
CREATE AGGREGATE util.max_by_cols(anyelement, text) (
SFUNC = util.max_by_cols,
STYPE = anyelement
);
CREATE or replace FUNCTION util.compare_by_cols(t1 anyelement, t2 anyelement, cols text) RETURNS boolean
LANGUAGE plpgsql IMMUTABLE STRICT AS
$$
DECLARE
lhs text;
rhs text;
r boolean;
BEGIN
SELECT '''$1.''||#' -< cols INTO lhs;
SELECT '''$2.''||#' -< cols INTO rhs;
EXECUTE format('SELECT (%1$s) < (%2$s)', lhs, rhs) INTO r USING t1, t2;
RETURN r;
END;
$$;
select (util.max_by_cols(x, 'util.compare_by_cols'::regproc, 'col1,col2,col3')).rc FROM test_t x;
select (util.max_by_cols(test_t, 'col1,col2,col3')).rc FROM test_t;
select (util.max_by_cols(x, 'col1,col2,col3')).rc FROM test_t x join test_t y on x.rc=y.rc;

虽然上面的代码可能适用于某些情况，但它有一些非常具体的限制。

1. 苯乙烯的形状被迫与表中的行形状相同。如果我们以这样一种方式折叠，需要额外的状态属性，这是不够的。例如，如果我想传递一个常量(比如优先级表中某个优先级列表的id)，我将被迫将该id存储在表行中以获得所需的形状，即使我不希望它存储在底层表中。

2. sfunc的第一个参数util.max_by_cols()是stype，但我们使用表名来填充该参数。看看上面的第三个例子，我将test_t x连接到test_t y，很明显，我必须使用其中一个表的别名(我在上面的例子中使用了x)。然而，在我的用例中，我需要的是能够从x和y两个表中传递列。具体地说，表x是我的主表，表y保存了我在表x中用于确定优先级的属性的优先级顺序。换句话说，我需要：

   select (util.max_by_cols(x join y, 'x.col1,x.col2,y.col3')).rc FROM test_t x join test_t y on x.rc=y.rc

也就是说，我的stype的形状是要连接的所有表的列的某个子集，加上在折叠过程中保持状态所需的任何任意项目。

通常，该函数应在以下上下文中工作：

select
t1.a,  -- the group by key(s)
sum(t1.b),  -- aggregating something in t1 or t2
f(t1.c, t2.d, t1.e, t1.f)  -- our new function, f, picking t1.c
-- using ordering of (t2.d, t1.e, t1.f)
from t1 join t2 on t1.da = t2.da   -- exactly one record in t2 matches
group by t1.a

总之，sql解决方案有两个主要问题，一是强制样式的形状(这限制了折叠的灵活性)，二是将样式的形状限制为仅来自单个表的列。的确，第二个限制可以通过子查询来解决，但我所处的环境中，查询的结构已经通过编程生成，我希望避免为此用例更改该结构。无论如何，苯乙烯的形状与行的形状相等的限制仍然是有问题的。我会更多地摆弄，但我真的很想看到C的解决方案。最终，看看如何在C中实现这一点，可能会在未来打开一个更有趣的可能性世界。

Answer 1

不需要C函数，只需使用自定义比较函数创建自己的聚合即可：

CREATE FUNCTION sfunc(anyelement, anyelement, regprocedure) RETURNS anyelement
LANGUAGE plpgsql AS
$$DECLARE
r boolean;
BEGIN
EXECUTE format('SELECT %s($1, $2)', $3::regproc) INTO r
USING $1, $2;
IF NOT r THEN
RETURN $1;
ELSE
RETURN $2;
END IF;
END;$$;
CREATE AGGREGATE mymax(anyelement, regprocedure) (
SFUNC = sfunc,
STYPE = anyelement
);

然后为t:定义一个自定义比较函数

CREATE FUNCTION t_lt(t1 t, t2 t) RETURNS boolean
LANGUAGE sql IMMUTABLE STRICT AS
'SELECT ROW(t1.col1, t1.col2, t1.col3) < ROW(t2.col1, t2.col2, t2.col3)';

现在您可以使用聚合：

SELECT (mymax(t, 't_lt'::regproc)).rc FROM t;
rc  
══════
Andy
(1 row)