gpt4 book ai didi

sql - 公用表表达式 : Relation does not exist

转载 作者:行者123 更新时间:2023-11-29 13:43:11 25 4
gpt4 key购买 nike

我使用的是 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;

当我运行此函数时,出现以下错误:

ERROR: ERREUR: la relation « cover_list » n'existe pas LINE 1: ...rom subscriptions.no_subscribers_cover inner join cover_list...

翻译:

(the relation « cover_list » does not exist )

我试图在查询窗口中仅运行 CTE,但我收到了相同的错误消息。

有什么我想念的吗?

最佳答案

CTE 是 SQL 语句的一部分,在它之外的任何地方都不可见。

因此,您只能在带有 WITH 子句的 SELECT 语句中使用 cover_list

要么在第二个 SELECT 语句中重复 WITH 子句,要么重构代码,这样您只需要一个查询。

另一种方法是使用临时表。

关于sql - 公用表表达式 : Relation does not exist,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/52363058/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com