설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


총 게시물 187건, 최근 0 건
   

SQL Error [42601]: ERROR: query has no destination for result data Hint: If you want to discard the results of a SELECT, use PERFORM instead. Where: PL/pgSQL function sp_resource_account_create(integer) line 6 at SQL statement

글쓴이 : 핑크팬더 날짜 : 2022-02-11 (금) 13:16 조회 : 1997
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$
;

위의 쿼리에서 해당 에러가 나는데 어느 부분이 잘못된는지 알려주실 수 있으실까요?ㅠㅠ

PostgresDBA 2022-02-14 (월) 12:34
에러메세지에 답이 있습니다.
아래 쿼리결과를 조회하고 버리네요? no into 절

select r.index_resource, r.word_index, r.resource_name  from resources r  where index_resource  = _resource_id limit 1;
댓글주소
핑크팬더 2022-02-14 (월) 13:24
금요일에 차근차근 다시 보면서 새삼 멍청하다는 생각을 해버렸습니다...
변수 선언도 두번 되어있고 말씀해주신 SELECT INTO를  빼먹어서 쿼리 낭비가 있었습니다..
댓글주소
   

postgresdba.com