Блог

Вот скрипт Postgresql. Я хочу превратить ее в функцию.

 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;
  

Здесь вы видите, что я жестко запрограммировал идентификатор клиента в 2 местах.Я хочу передать ее в качестве параметра функции.
Но это выдает синтаксическую ошибку при создании временной таблицы, когда я пытаюсь обернуть этот код внутри хранимой процедуры.

ОБНОВИТЬ Вот сценарий создания функции, который завершается с ошибкой:

 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$
  

И вот сообщение об ошибке, когда я пытаюсь сохранить функцию:

   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
  

ЕЩЕ ОДНО ОБНОВЛЕНИЕ
Я смог сохранить функцию после того, как прокомментировал все УВЕДОМЛЕНИЯ О ПОВЫШЕНИИ. Теперь, почему не работает raise notice?

в инструкции перед указанной строкой отсутствует точка с запятой

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

RAISE NOTICE 'Done with deleting survey_storage';