主页 / dba / 问题 / 37547
Accepted
Stephane Rolland
Asked: 2013-03-26 14:12:31 +0800 CST

setof 类型或 setof 记录的串联

  • 772

我将 Postgresql 9.1 与 Ubuntu 12.04 一起使用。

在一个plpgsql函数中，我尝试连接setof从另一个函数返回的类型。

有type pair_id_value问题是用create type pair_id_value as (id bigint, value integer);

返回基本的函数setof pair_id_value（稍后将连接的函数）是这个：

create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
    listResults = []
    for x in range(0,value+1):
        listResults.append({ "id": id, "value": x})
    return listResults
$$
language plpython3u;

这个直截了当的 plpython 代码应该很好，例如查询：select * from compute_pair_id_value(1712437,2);很好地返回：

  id            | value 
 ---------------+-----------
        1712437 |         0
        1712437 |         1
        1712437 |         2
 (3 rows)

这个python函数现在相当简单，对于这个例子，但最重要的是我的概念证明。在不久的将来它会变得更加复杂。


当我尝试从多个 id 连接所有结果表时，就会出现问题。

create or replace function compute_all_pair_id_value(id_obj bigint)
    returns setof pair_id_value as $$
declare
    pair pair_id_value;
begin
    for pair in (select compute_pair_id_value(t.id, t.obj_value) from my_obj as t where t.id = id_obj)
    loop
            return next pair;
    end loop;
    return; 
end; $$ language plpgsql;

我收到错误：invalid input syntax for integer "(1712437,0)"好像它不再被视为具有两列的 pair_id_value 而是作为元组 (1712437,0)。

所以我将函数的输出类型从 setof pair_id_value 更改为 setof record... 如果我执行这个类似的连接函数：

create or replace function compute_all_pair_id_value(id_obj bigint)
    returns setof record as $$
declare
    pair record;
begin
    for pair in (select compute_pair_id_value(t.id, t.obj_value)  from my_obj as t where t.id = id_obj)
    loop
            return next pair;
    end loop;
    return; 
end; $$ language plpgsql;

我得到错误：a column definition list is required for functions returning "record"

试图遵循这个 SO 问题的答案：我尝试以这种方式在 select 中定义列定义select compute_pair_id_value(t.id, t.obj_value) as f(id bigint, value integer)，完整的代码在这里：

create or replace function compute_all_pair_id_value(id_obj bigint)
    returns setof record as $$
declare
    pair record;
begin
    for pair in (select compute_pair_id_value(t.id, t.obj_value) as f(id bigint, value integer) from my_obj as t where t.id = id_obj)
    loop
            return next pair;
    end loop;
    return; 
end; $$ language plpgsql;

但是在启动 sql 脚本时，psql 不接受创建函数： syntax error at or near "(" select compute_pair_id_value(t.id, t.obj_value) as f(id bigint, value integer)... 将手指指向 f(

知道如何正确地做到这一点吗？

我应该考虑创建临时表来完成这项工作吗？

stored-procedures postgresql

1 个回答

  1. Best Answer

    您使用的方法过于复杂 - 而且效率非常低。而不是第一个功能使用：

    create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
SELECT $1, generate_series(0,$2);
$$                          
language sql;

    或者更好，完全摆脱它并像这样编写整个操作：

    -- Sample data creation:
CREATE TABLE my_obj(id bigint, obj_value integer);
insert into my_obj(id,obj_value) VALUES (1712437,2),(17000,5);

-- and the query:
SELECT id, generate_series(0,obj_value) FROM my_obj;

    导致：

    regress=> SELECT id, generate_series(0,obj_value) FROM my_obj;
   id    | generate_series 
---------+-----------------
 1712437 |               0
 1712437 |               1
 1712437 |               2
   17000 |               0
   17000 |               1
   17000 |               2
   17000 |               3
   17000 |               4
   17000 |               5
(9 rows)

    这利用了 PostgreSQL 在SELECT列表中调用的集合返回函数的行为。一旦 PostgreSQL 9.3 出来，它可以被一个符合标准的LATERAL查询替换。

    由于事实证明您的问题是实际问题的简化版本，让我们来解决这个问题。我将使用compute_pair_id_value上面的简化程序来避免 plpython3 的麻烦。以下是如何做你想做的事：

    SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;

    结果：

    regress=> SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;
   id    | value 
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

    但同样，请注意compute_pair_id_value将被多次调用。这是 PostgreSQL 查询执行器的一个限制，可以在 9.3 中通过LATERAL支持来避免，但据我所知，您在 9.2 及更低版本中被困住了。观察：

    create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
BEGIN
  RAISE NOTICE 'compute_pair_id_value(%,%)',id,value;
  RETURN QUERY SELECT $1, generate_series(0,$2);
END;
$$             
language plpgsql;

    输出：

    regress=> SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(17000,5)
NOTICE:  compute_pair_id_value(17000,5)
   id    | value 
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

    看看compute_pair_id_value每个输出列如何调用一次？

    有一个解决方法：另一层子查询来解包复合类型结果。看：

    regress=> SELECT (val).* FROM (SELECT compute_pair_id_value(id,obj_value) FROM my_obj) x(val);
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(17000,5)
   id    | value 
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

    如果你真的必须LOOP超过结果，你可以在你的代码中使用相同的技术（这样做很慢，所以如果可以的话，请避免它）。

    • 4

相关问题