python-3.x - PL/Python3 - 连接 PLyResults

Question

我想知道是否可以在函数内部以某种方式连接 PLyResults。例如，假设首先我有一个函数 _get_data，给定一个元组 (id, index) 返回一个值表：

CREATE OR REPLACE FUNCTION _get_data(id bigint, index bigint):
RETURNS TABLE(oid bigint, id bigint, val double precision) AS
$BODY$

#...process for fetching the data, irrelevant for the question.
return recs    

$BODY$
LANGUAGE plpython3u;

现在我希望能够创建一个这样定义的通用函数，它在给定 ID 的两个边界之间获取数据，并使用前面的函数单独获取数据，然后以某种方式聚合结果：

CREATE OR REPLACE FUNCTION get_data(id bigint, lbound bigint, ubound bigint)
RETURNS TABLE(oid bigint, id bigint, val double precision) AS
$BODY$

concatenated_recs = [] #<-- For the sake of argument.
plan = plpy.prepare("SELECT oid, id, val FROM _get_data($1, $2);", ['bigint', 'bigint'])
for i in range(lbound, ubound+1):
    recs = plpy.execute(plan, [id, i]) # <-- Records fetched individually 
    concatenated_recs += [recs] #<-- Not sure how to concatenate them...

return concatenated_recs

$BODY$
LANGUAGE plpython3u; 

标签： python-3.xpostgresqlplpython

Answer 1

也许我遗漏了一些东西，但你给出的答案看起来像是这个查询的一个更慢、更复杂的版本：

SELECT oid, id, val 
FROM generate_series(your_lower_bound, your_upper_bound) AS g(i),
_get_data(your_id, i);

你可以把它放在一个没有循环或临时表的简单 SQL 函数中：

CREATE OR REPLACE FUNCTION get_data(id bigint, lbound bigint, ubound bigint)
RETURNS TABLE(oid bigint, id bigint, val double precision) AS
$BODY$
SELECT oid, id, val 
FROM generate_series(lbound, ubound) AS g(i),
_get_data(id, i);
$BODY$ LANGUAGE SQL;