0

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?

Improve this question
2
  • Show us the code that gives you an error. Commented Aug 16, 2020 at 0:54
  • @Kamil, I have updated the question to show the code that gives error. Commented Aug 16, 2020 at 1:09

1 Answer 1

Reset to default
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';
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.