postgresql - 函数交叉表(未知，未知)不存在但确实存在

Question

我有一个交叉表函数，我过去曾多次成功使用它，但现在它在最后转储所有数据，而不是将其旋转到输出表中。它似乎无法找到交叉表。我通过以下方式对其进行了研究；

• 如果 tablefunc 不存在则创建扩展； --- 答案是:扩展名“tablefunc”已经存在
• 使用模式动物创建扩展表函数；答案是:如上
• select count(*) from information_schema.routines where routine_name like 'crosstab%'; ---- 答案是 6。

以下为部分功能代码:开始str := '""文本,'; -- A1单元格空白

FOR rec IN SELECT DISTINCT col_name 来自 an_in_tbl 按col_name排序 环形 海峡:=海峡 || '"' || rec.col_name || '"文本' ||','; 结束循环；

str:= substring(str, 0, length(str));

EXECUTE 'CREATE EXTENSION IF NOT EXISTS tablefunc;
DROP TABLE IF EXISTS an_out_tbl;
CREATE TABLE an_out_tbl AS
SELECT *
FROM crosstab(''select row_name, col_name, row_value from an_in_tbl order by 1'',   
             ''SELECT DISTINCT col_name FROM an_in_tbl ORDER BY 1'')
     AS final_result ('|| str ||')';

选择 animal_pivot_fn()

NOTICE: extension "tablefunc" already exists, skipping NOTICE: table "an_out_tbl" does not exist, skipping ERROR: function crosstab(unknown, unknown) does not exist LINE 5: FROM crosstab('select row_name, col_name, row_value from... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: CREATE EXTENSION IF NOT EXISTS tablefunc; DROP TABLE IF EXISTS an_out_tbl; CREATE TABLE an_out_tbl AS SELECT * FROM crosstab('select row_name, col_name, row_value from an_in_tbl order by 1',
'SELECT DISTINCT col_name FROM an_in_tbl ORDER BY 1') AS final_result (" " text,"CAT" text,"DOG" text,"SNAKE" text,"HORSE" text,"ELEPHANT" text,"MOUSE" text,"MONKEY"... and many more... HERE IS WHERE THE DATA GETS DUMPED AND NO PIVOTED TABLE GETS CREATED.

Answer 1

需要运行下面的查询

运行 \dx 命令。

如果像下面的结果需要运行下面的查询

 CREATE EXTENSION tablefunc;

再次运行\dx命令，结果应该如下所示。

现在你可以运行交叉表查询了，应该就解决了。