我正在使用PostgreSQL版本10.3，与函数内的公共表达式一起工作。以下是该函数的代码：
CREATE OR REPLACE FUNCTION subscriptions.to_supply_patients(
character varying,
timestamp with time zone)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
declare
aws_sub_pro alias for $1; -- health professional aws_sub
tour_date alias for $2; -- tour date to manage patients covered accounts
number_cover int; -- the number of account to manage
sub_list integer[]; -- array list for subscribers covered
no_sub_list integer[];-- array list for no subscribers covered
date_tour timestamp with time zone;--tour date to manage patients covered accounts converted with time zone converted to 23:59:59
begin
select subscriptions.convert_date_to_datetime((select date(tour_date)),'23:59:59','0 days') into date_tour; -- function to convert time to 23:59:59
select count(*) from subscriptions.cover where aws_sub = aws_sub_pro into number_cover; -- global number of patient to cover
if number_cover > 0 then
begin
if tour_date >= current_timestamp then -- if tour date is later than today date
begin
with cover_list as (
select id_cover from subscriptions.cover where aws_sub = aws_sub_pro and cover_duration >0 and is_covered_auto = true
-- we selectionned here the patients list to cover for health pro with aws_sub = aws_sub_pro
),
sub_cover_list as (
select id_subs from subscriptions.subscribers_cover inner join cover_list on cover_list.id_cover = subscribers_cover.id_cover
-- list of subscribers covered
),
no_sub_cover_list as (
select id_free_trial from subscriptions.no_subscribers_cover inner join cover_list on cover_list.id_cover = no_subscribers_cover.id_cover
-- list of no subscribers covered
)
-----------------------------------------------------------------------------------------------------------------------------
select array( select id_subs from subscriptions.subscribers_cover inner join cover_list on cover_list.id_cover = subscribers_cover.id_cover
) into sub_list; -- convert list of subscribers covered into array list
if array_upper(sub_list,1) <>0 then -- if array list is not empty
begin
for i in 1 .. array_upper (sub_list,1) loop -- for every one in this list
if date_tour = (select sub_deadline_date from subscriptions.subscription where id_subs =sub_list[i] ) then -- if tour date is equals to
-- the deadline date
begin
update subscriptions.subscription
set
sub_expiration_date = sub_expiration_date + interval'30 days', -- add 30 days to the exp date
sub_deadline_date = sub_deadline_date + interval'30 days', -- add 30 date to deadline date
sub_source = aws_sub_pro, -- supply source is no the professional
is_sub_activ = false -- we turn off patients subscription
where id_subs = (select id_subs from subscriptions.subscription where id_subs =sub_list[i] );
end;
end if;
end loop;
end;
end if;
--------------------------------------------------------------------------------------------------------------------------------
select array(select id_free_trial from subscriptions.no_subscribers_cover inner join cover_list on cover_list.id_cover = no_subscribers_cover.id_cover
) into no_sub_list;
if array_upper(no_sub_list,1) <>0 then
begin
for i in 1 .. array_upper (no_sub_list,1) loop
if date_tour = (select expiration_date from subscriptions.free_trial where id_free_trial =no_sub_list[i] and is_free_trial_activ = true ) then
begin
update subscriptions.free_trial
set
expiration_date = expiration_date + interval'30 days'
where id_free_trial = (select id_free_trial from subscriptions.free_trial where id_free_trial =no_sub_list[i] );
end;
end if;
end loop;
end;
end if;
end;
else
raise 'tour date must be later than today''s date' using errcode='71';
end if;
end;
else
raise notice 'your cover list is empty. you don''t have any patient to cover' using errcode='70';
end if;
end;
$BODY$;
ALTER FUNCTION subscriptions.to_supply_patients(character varying, timestamp with time zone)
OWNER TO master_pgsql_hygeexv2;
当我运行此函数时，出现以下错误：
翻译：错误: ERREUR: 关系“cover_list”不存在 LINE 1: ...rom subscriptions.no_subscribers_cover inner join cover_list...
我尝试在查询窗口中仅运行CTE，但是仍然收到相同的错误消息。(关系“cover_list”不存在)
是否有什么我遗漏的内容？
select array(select id_free_trial from subscriptions.no_subscribers_cover inner join cover_list- Łukasz Kamiński