PostgreSQL存储过程内调用colpivot报Query has no result错误

colpivot函数执行时会动态生成指定名称的临时表，同时返回对应的透视结果集。在PL/pgSQL存储过程中，裸SELECT语句如果没有明确的结果接收目标（比如赋值给变量、写入表），数据库不会完整执行函数的全部逻辑，就会抛出Query has no result in destination data错误。
在客户端单独执行语句能正常运行，是因为SQL客户端会默认消费SELECT返回的结果集，函数内部的临时表生成逻辑可以完整执行；但存储过程中没有结果接收动作时，函数执行被截断，临时表未正常生成，后续建表读数据的逻辑自然也会触发异常。

优先选择第一种方案，改动最小：

方案1：使用PERFORM执行colpivot，丢弃不需要的返回结果 #

后续逻辑是直接读取colpivot生成的_report临时表来创建物理表，不需要直接使用colpivot返回的结果集，用PL/pgSQL内置的PERFORM关键字执行函数即可——它会完整运行函数的全部逻辑生成临时表，同时丢弃冗余的返回结果，不会触发结果目标缺失的报错。
修改后的完整存储过程代码：

create or replace procedure create_report_table()
language plpgsql
as $$
begin

drop table if exists reports;

-- 将原SELECT改为PERFORM即可
perform colpivot('_report',
'select
u.username,
c.shortname as course_short_name,
to_timestamp(cp.timecompleted)::date as completed
FROM mdl_course_completions AS cp
JOIN mdl_course AS c ON cp.course = c.id
JOIN mdl_user AS u ON cp.userid = u.id
WHERE c.enablecompletion = 1
ORDER BY u.username' ,array['username'], array['course_short_name'], '#.completed', null);

create table reports as (SELECT * FROM _report);

commit;
end; $$

方案2：显式指定结果写入目标 #

如果不想使用PERFORM，也可以显式将colpivot的返回结果写入临时表，给查询明确的结果目的地，同样可以解决报错。注意执行前需要先清理可能存在的同名临时表，避免重名冲突：

-- 先清理同名临时表
drop table if exists _report;
-- 显式将colpivot结果写入临时表
create temp table _report as
select colpivot('_report',
'select
u.username,
c.shortname as course_short_name,
to_timestamp(cp.timecompleted)::date as completed
FROM mdl_course_completions AS cp
JOIN mdl_course AS c ON cp.course = c.id
JOIN mdl_user AS u ON cp.userid = u.id
WHERE c.enablecompletion = 1
ORDER BY u.username' ,array['username'], array['course_short_name'], '#.completed', null);

修改完成后执行调用命令即可正常生成报表物理表：

call create_report_table();

内容的提问来源于stack exchange，提问作者Nilaksha Perera