|
## Function 생성코드 #############################
CREATE FUNCTION public.func_Stat(INT,TEXT)
RETURNS SETOF RECORD as '
DECLARE
v_code ALIAS FOR $1;
v_detail ALIAS FOR $2;
row RECORD;
BEGIN
IF v_code = 1 THEN
FOR row IN EXECUTE ''
select
data_date, data_hour,
sum(NonConnected) as NonConnected,
sum(Connected) as Connected,
count(*) as connectCount,
sum(connectingtime) as TotalCallTime,
avg(connectingtime) as AvgCallTime
from (
select
to_char(setup_time,''YYYY-MM-DD'') as
data_date,
to_char(setup_time,''HH'') as data_hour,
(case when connect_time is null then 1 else 0 end)
as NonConnected,
(case when connect_time is null then 0 else 1 end)
as Connected,
(disconnect_time - connect_time) as
connectingtime
from cdr
where setup_time like (v_detail || ''%'')
) as t_connT
group by data_date, data_hour
''
LOOP
RETURN NEXT row;
END LOOP;
END IF;
RETURN;
END;'
LANGUAGE 'plpgsql';
################################################
## Error Code ####################################
>[Error] Script lines: 1-2 --------------------------
ERROR: parser: parse error at or near "YYYY" at character 353
Warnings: --->
W (1): Error occurred while executing PL/pgSQL function
func_stat
W (2): line 7 at for over execute statement
<---
################################################
## Description #####################################
첫번째 인수값에 따라 선택적인 query를 실행하는 Function의
단편입니다.
위 에러 코드를 봐서는 to_char(TimeStamp변수,'YYYY') 와 같은
식에서 '' 부분에서
외각에 바디를 감싸고 있는 '' 부분하고의 문제로
인해 에러가 나는듯 한데 '''' 으로 변경하여도
똑같은 에러가 발생한다.
Function 안에 설정된 Query문장은 단독 Query 문으로 실행했을
경우,
문제 없이 실행되며 올바른 결과가 나온다.
################################################
## ETC ##########################################
여러 고수님들의 조언을 경청하겠습니다.
많은 답변 부탁드립니다.
갓 Postgresql 을 시작한 초보 개발자 올림
################################################
|
