Can I create a temporary table from inside a Postgresql function?

Question

Here is the Postgresql script. I want to turn it into a function.

CREATE TEMPORARY TABLE bad_survey (
survey_id int8 NOT NULL,
template_id int8 NOT NULL
);
analyze bad_survey;
insert into bad_survey(survey_id, template_id)
(select id as survey_id, template_id 
from survey_storage 
where status in ('Never Surveyed','Incomplete Configuration','Ready to Launch')
and id=original_row_id 
and tenant_id=owner_tenant_id
and tenant_id=5);
insert into bad_survey(survey_id, template_id)
(select pss.id, pss.template_id
 from survey_storage css
    inner join company_by_path cbp
        on css.company_by_path_id = cbp.id
        and css.tenant_id = cbp.tenant_id   
        and cbp.relationship_type = 'partner'
    inner join survey_storage pss
        on cbp.owner_tenant_id = pss.tenant_id
        and css.master_template_id = pss.master_template_id
        and css.tenant_id = pss.owner_tenant_id
        and css.source_id = pss.source_id
        and css.tenant_id != pss.tenant_id
        and css.template_id != pss.template_id
        and pss.id != pss.original_row_id
 where css.id in (select id as survey_id
                from survey_storage 
                where status in ('Never Surveyed','Incomplete Configuration','Ready to Launch')
                and id=original_row_id 
                and tenant_id=owner_tenant_id
                and tenant_id=5));
DELETE FROM survey_user su
    USING bad_survey bs
    WHERE su.survey_id = bs.survey_id;

DELETE FROM survey_library_users slu
    USING bad_survey bs
    WHERE slu.survey_library_id = bs.template_id;

DELETE FROM row_history rh
    USING bad_survey bs
    WHERE rh.row_id = bs.survey_id;

DELETE FROM survey_storage ss
    USING bad_survey bs
    WHERE ss.id = bs.survey_id;

DELETE FROM survey_library sl
    USING bad_survey bs
    WHERE sl.id = bs.template_id;

Here you I see I hard-coded tenant id at 2 places.I want to pass it as a function parameter. But it gives syntax error at create temporary table when I try to wrap this code inside a stored procedure.

UPDATE Here is the function create script that fails:

CREATE OR REPLACE FUNCTION public.purge_bad_surveys(_tenant_id bigint)
RETURNS bool
LANGUAGE plpgsql
AS $function$ 
BEGIN
CREATE TEMPORARY TABLE bad_survey (
survey_id int8 NOT NULL,
template_id int8 NOT NULL
);
analyze bad_survey;
insert into bad_survey(survey_id, template_id)
(select id as survey_id, template_id 
from survey_storage 
where status in ('Never Surveyed','Incomplete Configuration','Ready to Launch')
and id=original_row_id 
and tenant_id=owner_tenant_id
and tenant_id=_tenant_id);
insert into bad_survey(survey_id, template_id)
(select pss.id, pss.template_id
 from survey_storage css
    inner join company_by_path cbp
        on css.company_by_path_id = cbp.id
        and css.tenant_id = cbp.tenant_id   
        and cbp.relationship_type = 'partner'
    inner join survey_storage pss
        on cbp.owner_tenant_id = pss.tenant_id
        and css.master_template_id = pss.master_template_id
        and css.tenant_id = pss.owner_tenant_id
        and css.source_id = pss.source_id
        and css.tenant_id != pss.tenant_id
        and css.template_id != pss.template_id
        and pss.id != pss.original_row_id
 where css.id in (select id as survey_id
                from survey_storage 
                where status in ('Never Surveyed','Incomplete Configuration','Ready to Launch')
                and id=original_row_id 
                and tenant_id=owner_tenant_id
                and tenant_id=_tenant_id));
DELETE FROM survey_user su
    USING bad_survey bs
    WHERE su.survey_id = bs.survey_id;
RAISE NOTICE 'Done with deleting survey users';
DELETE FROM survey_library_users slu
    USING bad_survey bs
    WHERE slu.survey_library_id = bs.template_id;
RAISE NOTICE 'Done with deleting survey_library_users';
DELETE FROM row_history rh
    USING bad_survey bs
    WHERE rh.row_id = bs.survey_id;
 
RAISE NOTICE 'Done with deleting row_history';

DELETE FROM survey_storage ss
    USING bad_survey bs
    WHERE ss.id = bs.survey_id

RAISE NOTICE 'Done with deleting survey_storage';

DELETE FROM survey_library sl
    USING bad_survey bs
    WHERE sl.id = bs.template_id;


RAISE NOTICE 'Done with deleting survey_library'; 

return true;
end;
 $function$

And here is the error message when I try to persist the function:

  SQL Error [42601]: ERROR: syntax error at or near "RAISE"
  Position: 1893
  ERROR: syntax error at or near "RAISE"
  Position: 1893
  ERROR: syntax error at or near "RAISE"
  Position: 1893

ANOTHER UPDATE I have been able to persist the function after commenting out all RAISE NOTICE. Now why raise notice is not working?

@Kamil, I have updated the question to show the code that gives error. — Subhendu Mahanta
– Subhendu Mahanta, Commented Aug 16, 2020 at 1:09

JGH · Accepted Answer · 2020-08-17 02:13:45Z

1

you are missing a semi-colon in the statement before the reported line

DELETE FROM survey_storage ss
    USING bad_survey bs
    WHERE ss.id = bs.survey_id; -- <----------------------  HERE

RAISE NOTICE 'Done with deleting survey_storage';

answered Aug 17, 2020 at 2:13

JGH

18.2k5 gold badges40 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Subhendu Mahanta Over a year ago

Sorry for not updating the question with the correction, I found that when I commented out ''raise notice" lines & still it was not compiling.But after that semi-colon correction still it fails at raise notice. However I have been told my code is correct, only the ide DBeaver from where I am executing the code cannot handle raise notice or any other raise statement. We will directly run the code in server from shell and see if it gives any problem.

Collectives™ on Stack Overflow

Can I create a temporary table from inside a Postgresql function?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related