如何在結果表定義未知的情況下生成旋轉的 CROSS JOIN？

給定兩個具有未定義行數且具有名稱和值的表，我將如何CROSS JOIN在它們的值上顯示一個函式的透視。

CREATE TEMP TABLE foo AS
SELECT x::text AS name, x::int
FROM generate_series(1,10) AS t(x);

CREATE TEMP TABLE bar AS
SELECT x::text AS name, x::int
FROM generate_series(1,5) AS t(x);

例如，如果該函式是乘法，我將如何生成如下所示的（乘法）表，

所有這些(arg1,arg2,result)行都可以生成

SELECT foo.name AS arg1, bar.name AS arg2, foo.x*bar.x AS result
FROM foo
CROSS JOIN bar; 

所以這只是一個展示問題，我希望這也可以使用自定義名稱——這個名稱不僅僅是CAST文本的參數，而是設置在表格中，

CREATE TEMP TABLE foo AS
SELECT chr(x+64) AS name, x::int
FROM generate_series(1,10) AS t(x);

CREATE TEMP TABLE bar AS
SELECT chr(x+72) AS name, x::int
FROM generate_series(1,5) AS t(x);

我認為使用具有動態返回類型的 CROSSTAB 很容易做到這一點。

SELECT * FROM crosstab(
 '
   SELECT foo.x AS arg1, bar.x AS arg2, foo.x*bar.x
   FROM foo
   CROSS JOIN bar
 ', 'SELECT DISTINCT name FROM bar'
) AS **MAGIC**

但是，沒有**MAGIC**，我得到

ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM crosstab(

作為參考，使用上面的例子和名字這更像是tablefunc什麼crosstab()。

SELECT * FROM crosstab(
 '
   SELECT foo.x AS arg1, bar.x AS arg2, foo.x*bar.x
   FROM foo
   CROSS JOIN bar
 '
) AS t(row int, i int, j int, k int, l int, m int);

但是，現在我們回到對bar範例中表格的內容和大小進行假設。因此，如果，

1. 表格的長度未定義，
2. 然後交叉連接代表一個未定義維度的立方體（由於上述原因），
3. 類別名稱（交叉表用語）在表中

在沒有“列定義列表”的情況下，我們在 PostgreSQL 中能做的最好的事情是什麼來生成這種表示？

簡單案例，靜態SQL

對於簡單情況的非動態解決方案：crosstab()

SELECT * FROM crosstab(
 'SELECT b.x, f.name, f.x * b.x AS prod
  FROM   foo f, bar b
  ORDER  BY 1, 2'
  ) AS ct (x int, "A" int, "B" int, "C" int, "D" int, "E" int
                , "F" int, "G" int, "H" int, "I" int, "J" int);

我將結果列排序為foo.name，而不是foo.x。兩者恰好是並行排序的，但這只是簡單的設置。為您的案例選擇正確的排序順序。第二列的實際值與此查詢無關（的 1 參數形式crosstab()）。

我們甚至不需要crosstab()2 個參數，因為根據定義沒有缺失值。看：

• PostgreSQL 交叉表查詢

（您通過在以後的編輯中替換為修復了問題中的交叉表查詢foo。bar這也修復了查詢，但繼續使用來自 的名稱foo。）

未知返回類型，動態 SQL

列名和類型不能是動態的。SQL 要求在呼叫時知道結果列的數量、名稱和類型。通過顯式聲明或系統目錄中的資訊（這就是發生的情況SELECT * FROM tbl：Postgres 查找已註冊的表定義。）

您希望 Postgres 從使用者表中的 數據派生出結果列。不會發生。

一種或另一種方式，您需要兩次往返伺服器。要麼創建一個游標，然後遍歷它。或者您創建一個臨時表，然後從中進行選擇。或者您註冊一個類型並在呼叫中使用它。

或者您只需在一個步驟中生成查詢並在下一步中執行它：

SELECT $$SELECT * FROM crosstab(
 'SELECT b.x, f.name, f.x * b.x AS prod
  FROM   foo f, bar b
  ORDER  BY 1, 2'
  ) AS ct (x int, $$
|| string_agg(quote_ident(name), ' int, ' ORDER BY name) || ' int)'
FROM   foo;

這會動態生成上面的查詢。在下一步中執行它。

我使用美元引號 ( $$) 來簡化嵌套引號的處理。看：

• 在 PostgreSQL 中插入帶單引號的文本

quote_ident()對於轉義其他非法的列名（並可能防禦 SQL 注入）是必不可少的。

有關的：

• PostgreSQL 將列轉換為行？轉置？
• 使用 CASE 和 GROUP BY 的動態替代方案

在沒有“列定義列表”的情況下，我們在 PostgreSQL 中能做的最好的事情是什麼來生成這種表示？

如果您將此作為一個展示問題，您可能會考慮一個查詢後展示功能。

(9.6)的較新版本psql附帶\crosstabview，在沒有 SQL 支持的情況下顯示交叉表表示的結果（因為 SQL 無法直接生成此結果，如@Erwin 的回答中所述：SQL 要求在呼叫時知道結果列的編號、名稱和類型)

例如，您的第一個查詢給出：

SELECT foo.name AS arg1, bar.name AS arg2, foo.x*bar.x AS result
FROM foo
CROSS JOIN bar
\crosstabview

arg1 | 1  | 2  | 3  | 4  | 5  
------+----+----+----+----+----
1    |  1 |  2 |  3 |  4 |  5
2    |  2 |  4 |  6 |  8 | 10
3    |  3 |  6 |  9 | 12 | 15
4    |  4 |  8 | 12 | 16 | 20
5    |  5 | 10 | 15 | 20 | 25
6    |  6 | 12 | 18 | 24 | 30
7    |  7 | 14 | 21 | 28 | 35
8    |  8 | 16 | 24 | 32 | 40
9    |  9 | 18 | 27 | 36 | 45
10   | 10 | 20 | 30 | 40 | 50
(10 rows)

帶有 ASCII 列名的第二個範例給出：

SELECT foo.name AS arg1, bar.name AS arg2, foo.x*bar.x
   FROM foo
   CROSS JOIN bar
 \crosstabview

arg1 | I  | J  | K  | L  | M  
------+----+----+----+----+----
A    |  1 |  2 |  3 |  4 |  5
B    |  2 |  4 |  6 |  8 | 10
C    |  3 |  6 |  9 | 12 | 15
D    |  4 |  8 | 12 | 16 | 20
E    |  5 | 10 | 15 | 20 | 25
F    |  6 | 12 | 18 | 24 | 30
G    |  7 | 14 | 21 | 28 | 35
H    |  8 | 16 | 24 | 32 | 40
I    |  9 | 18 | 27 | 36 | 45
J    | 10 | 20 | 30 | 40 | 50
(10 rows)

有關更多資訊，請參閱psql 手冊和https://wiki.postgresql.org/wiki/Crosstabview。

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