查询在pgsql存储过程中调用colpivot时没有结果数据
我已经创建了一个程序来使用colpivot https://github.com/ 并将结果保存到PGSQL
create or replace procedure create_report_table()
language plpgsql
as $$
begin
drop table if exists reports;
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);
create table reports as (SELECT * FROM _report);
commit;
end; $$
COLPIVOT功能，drop Table，Delete表中的物理表中，删除表在隔离中确实很好。但是，当我按以上创建过程并调用执行过程时，这会引发错误 查询没有结果数据
我可以在任何方法中使用colpivot与我当前尝试的几个查询合作？
I have created a procedure to generate temp table using colpivot https://github.com/hnsl/colpivot
and saving the result into a physical table as below in PGSQL
create or replace procedure create_report_table()
language plpgsql
as $
begin
drop table if exists reports;
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);
create table reports as (SELECT * FROM _report);
commit;
end; $
colpivot function , drop table , delete table works really fine in isolation. but when I create the procedure as above, and call the procedure to execute, this throws an error Query has no result in destination data
Is there any way I can use colpivot in collaboration with several queries as I am currently trying ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
使用表演而不是选择。这将执行该语句，而无需将结果保留在某个地方。这就是
Use PERFORM instead of SELECT. That will execute the statement, without the need to keep the result somewhere. This is what the manual says: