setof 類型或 setof 記錄的串聯

我將 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(

知道如何正確地做到這一點嗎？

我應該考慮創建臨時表來完成這項工作嗎？

您使用的方法過於復雜 - 而且效率非常低。而不是第一個功能使用：

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超過結果，你可以在你的程式碼中使用相同的技術（這樣做很慢，所以如果可以的話，請避免它）。

引用自：https://dba.stackexchange.com/questions/37547