[GPDB] 8. Functions

Internal Function

• now() vs timeofday()
  - now, current_timestamp : 1개의 transaction 내에서는 값 동일
  - timeofday, clock_timestamp : 1개의 transaction 내에서 값 다름

 

(참고) 기본 함수들 

---

SQL Functions

CREATE FUNCTION public.clean_customer ()
RETURNS void
AS 'DELETE FROM dimensions.customer WHERE state = ''NA''; '
LANGUAGE SQL;

SELECT public.clean_customer ();
clean_customer
-----------
(1 row)

 

• Retuning Set

CREATE FUNCTION dimensions.getstatecust (char)
RETURNS SETOF customer AS
$$
   SELECT * FROM
   dimensions.customer
   WHERE state = $1;
$$
LANGUAGE SQL;

SELECT *, UPPER(city)
FROM dimensions.getstatecust ('');

 

---

PL/pgSQL

• SQL Function 은 선언 후 SQL 문 실행을 한다면, 
• PL/pgSQL은 추가로 로직처리(변수, 제어문, loop, exception ... ) 가능
• [Function body] 내부에서 loop 등 실행 가능..

• Dynamic SQL
  - Function 이 실행되는 시점에 SQL이 파싱되도록 하는 sql문

• 예외 처리

 

• Function 으로 제공 가능 한 것
  • 1) Cost 
    - main 에서 여러 function 을 호출하는 경우, 
      main 실행 시 따로 cost 를 계산하지 않고 default 값으로 계산하기 때문에 cost 가 정확하지 않을 수 있음.
    - 이 때 각각의 function의 cost 에 대해 function 으로 정보를 제공해줄 수 있음.

 

• 2) configuration parameter
  - 할당된 용량을 사용하여 function 을 수행하여 성능이 좋아지는 경우가 있음.
  - 다만, 작은 용량으로 수행이 가능하더라도, 할당을 해둔 용량을 다 받을 때까지 대기한 후 function 수행하여 느리게 수행될 수 있음.

---

Anonymous Function

• function 에 body만 있는 구문 (param, return 등 x)
• 이름 없이 sql 처럼 돌지만, loop 등 비교적 복잡한 구문을 실행할 수 있음.

---

OLAP Function

GROUP BY ROLLUP ( col1, col2, col3 )
GROUP BY CUBE ( col1, col2, col3 )
GROUP BY GROUPING SETS ( (c1, c2), (c1, c3))

 

WINDOW FUNCTION

 

---

실습

--integer argument 를 필터값으로 사용하여 setof row 를 리턴하는 함수 생성
CREATE FUNCTION training.getairlines(integer) 
 RETURNS SETOF training.d_airlines
AS 
$$
 SELECT * FROM training.d_airlines 
 WHERE airlineid <= $1; 
$$ 
LANGUAGE sql;

SELECT * FROM training.getairlines(19414);

 

--세 개의 정수값을 붙여서 날짜로 변환하는 함수 생성
CREATE OR REPLACE FUNCTION training.fn_concat(p_year integer, p_month integer, p_day integer)
RETURNS DATE AS 
$$
DECLARE
 v_return_text TEXT;
 v_return_date DATE;
BEGIN
 v_return_text := p_year || '-' || p_month || '-' || p_day;
 v_return_date := v_return_text::DATE;
 RETURN v_return_date;
END;
$$
LANGUAGE plpgsql;

SELECT training.fn_concat(2017, 9, 28);
SELECT training.fn_concat(2017, 9, 32); --오류 발생(날짜 넘어감)

 

--fn_concat 를 호출하기 전에 3 개의 argument 를 체크하는 function 생성
CREATE OR REPLACE FUNCTION training.fn_concat_check
 (p_year integer, p_month integer, p_day integer)
RETURNS DATE AS $$
BEGIN
-- validate year
IF p_year < 1950 or p_year > 2050 THEN
RAISE EXCEPTION 'Input YEAR (%) is invalid (should be 1950 to 2050 incl.)!', p_year;
END IF;
-- validate month
IF p_month < 1 or p_month > 12 THEN
RAISE EXCEPTION 'Input MONTH (%) is invalid (should be between 1 and 12 incl.)!', 
p_month;
END IF;
-- validate day
IF p_day < 1 or p_day > 31 THEN
RAISE EXCEPTION 'Input day (%) is invalid (should be between 1 and 31 incl.)!', p_day;
ELSIF (p_month = 2 AND p_day > 29) THEN
RAISE EXCEPTION 'Input month/day (%/%) is invalid (should be between 1 and 29 incl. for 
February)!', p_month, p_day;
ELSIF (p_month = 4 OR p_month = 6 OR p_month = 9 OR p_month = 11) AND p_day > 30 THEN
RAISE EXCEPTION 'Input month/day (%/%) is invalid (should be between 1 and 30 incl. for 
this month)!', p_month, p_day;
END IF;
RETURN training.fn_concat(p_year, p_month, p_day);
END;
$$
LANGUAGE plpgsql;; 

SELECT training.fn_concat_check(2011, -1, 28);--Invalid Month
SELECT training.fn_concat_check(2011, 2, 34); --Invalid Day
SELECT training.fn_concat_check(2011, 2, 30); --Invalid Month/Day
SELECT training.fn_concat_check(9999, -1, -1);--Invalid Year
SELECT training.fn_concat_check(2011,9,28); --OK

 

--DO BLOCK 이용하여 권한 관리 예시
SELECT table_schema, table_name, grantor, grantee, privilege_type 
 FROM information_schema.role_table_grants 
WHERE grantee != 'gpadmin' 
 AND table_schema = 'training'
ORDER BY grantee;

DO 
$$
DECLARE
 r record;
 v_table text;
BEGIN
 FOR r IN SELECT quote_ident(table_schema) as table_schema
 , quote_ident(table_name) as table_name
 , table_type
 FROM information_schema.tables
 WHERE table_schema = 'training'
 LOOP
 v_table := r.table_schema || '.' || r.table_name ;
 if r.table_type = 'VIEW' then
 RAISE NOTICE 'Granted View: %', v_table ;
 EXECUTE 'GRANT SELECT ON ' || v_table || ' TO reader';
 elseif r.table_type = 'BASE TABLE' then
 EXECUTE 'GRANT SELECT,INSERT ON ' || v_table || ' TO reader';
 RAISE NOTICE 'Granted Table: %', v_table;
 end if;
 END LOOP;
END
$$;

SELECT table_schema, table_name, grantor, grantee, privilege_type 
 FROM information_schema.role_table_grants 
WHERE grantee = 'reader' 
 AND table_schema = 'training';

 

--OLAP 함수를 이용하여 데이터 조회
SELECT distinct flt_year
 , flt_month
 , da.airline_desc
 , count(*) over (partition by flt_year,flt_month,f.airlineid) 
as nb_flights_per_month
 , count(case when cancelled <> 0 then 1 end) 
 over(partition by flt_year,flt_month,f.airlineid)
as nb_cancelled_flights_per_month
 , max(coalesce(arrdelay,0)) 
 over(partition by flt_year,flt_month,f.airlineid) 
as max_delay_per_month
 FROM training.f_otp_c f 
INNER JOIN training.d_airlines da 
 ON f.airlineid = da.airlineid 
ORDER BY max_delay_per_month desc limit 10