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 ?

CodePudding user response：

Use PERFORM instead of SELECT. That will execute the statement, without the need to keep the result somewhere. This is what the manual says:

Sometimes it is useful to evaluate an expression or SELECT query but discard the result, for example when calling a function that has side-effects but no useful result value. To do this in PL/pgSQL, use the PERFORM statement