CREATE OR REPLACE FUNCTION public.sp_resource_account_create(_resource_id integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$
declare
_cnt int;
_resource_id int4;
begin
select r.index_resource, r.word_index, r.resource_name from resources r where index_resource = _resource_id limit 1;
if (_resource_id is null) or (_resource_id = 0) then
return 'Not Registered Asset ID';
else
select count("userId") into _cnt
from (select u."userId" , a.index_resource as index_resource
from "user" u left outer join (select ra."userId", ra.index_resource as index_resource
from resource_account ra
where ra.index_resource = _resource_id) as a on u."userId" = a."userId") as k
where index_resource is null;
if (_cnt is null) or (_cnt = 0) then
return 'Every User Had Assigned This Asset';
else
insert into resource_account ("userId", index_resource, amount)
select k."userId",_resource_id ,0
from
(select
u."userId" as "userId" ,
a.index_resource as index_resource
from "user" u
left outer join (select ra."userId", ra.index_resource as index_resource
from resource_account ra
where ra.index_resource = _resource_id) as a
on u."userId" = a."userId") as k
where index_resource is null;
end if;
return 'user assigned';
end if;
return _cnt;
end;$function$
;
위의 쿼리에서 해당 에러가 나는데 어느 부분이 잘못된는지 알려주실 수 있으실까요?ㅠㅠ
|