SQL - PostgreSQL, Function, Procedure

PostgreSQL

 

 

✅ Function & Procedure

---

Function과 Procedure는 Database Object다.

이 둘을 이용하면 특정 작업을 수행하는 SQL구문을 세트로 캡슐화 할 수 있다.

(재사용 가능)

 

 

✅ Function

---

Function은 반드시 value를 return한다.

(Procedure와의 가장 큰 차이점)

 

DML(Data Manipulation Language) Command에서 사용하기 위한 것

CREATE OR REPLACE FUNCTION hello_world ()
RETURNS TEXT AS
$$
	SELECT 'hello_world';
$$
LANGUAGE SQL;

LANGUAGE 키워드로 어떤 언어로 Function이 작성 되었는지 적어줘야 한다.

 

CREATE OR REPLACE FUNCTION public.hello_world()
 RETURNS text
 LANGUAGE sql
AS $function$
	SELECT 'hello_world';
$function$

생성된 Function 모습

Function은 default로 public schema에 생성된다.

 

Function 호출방법

CREATE OR REPLACE FUNCTION hello_world (user_name TEXT)
RETURNS TEXT AS
$$
	SELECT 'hello ' || user_name;
$$
LANGUAGE SQL;

SELECT
	title,
	hello_world ('nico')
FROM
	movies;

Function의 arg에 arg를 안 넣어서 호출한다고 해서 Error가 나는 것이 아니라 잘 실행이 된다.(hello world가 나옴)

 

그 이유는 사실 PostgreSQL이 이름이 같다는 이유로 Function을 다르게 보지 않기 때문.

그래서 Functions를 확인해보면 같은 이름의 함수 2개가 존재하게 되고 arg가 없는 버전의 Function이 실행이 된 것.

 

 

arg를 이름 지정 없이 받는 법

CREATE
OR REPLACE FUNCTION hello_world (TEXT, TEXT) RETURNS TEXT AS $$
	SELECT 'hello ' || $1 || ' and ' || $2;
$$ LANGUAGE SQL;

SELECT
	hello_world ('nico', 'lynn');

 

 

Function에서 return 값을 Table로 주는 방법과 해당 Table의 column을 분리해서 나타내는 법

CREATE
OR REPLACE FUNCTION is_hit_or_flop (movie movies) RETURNS TABLE (hit_or_flop TEXT, other_thing NUMERIC) AS $$
	SELECT CASE
		WHEN movie.revenue > movie.budget THEN 'Hit'
		WHEN movie.revenue < movie.budget THEN 'Flop'
		ELSE 'N/A'
	END, 1515;
$$ LANGUAGE SQL;


SELECT
	title,
	(is_hit_or_flop (movies.*)).*
FROM
	movies;

 

 

Volatility

Volatility: optimizer에게 function의 동작에 대한 약속을 하는 것

기본적으로 함수를 만들면 Volatility는 Volatile이 된다.

 

Options

1. VOLATILE (default)

database를 수정하는 것을 포함해서 모든 것을 할 수 있다. (record를 지우거나 업데이트 하는 등)

VOLATILE Function은 같인 arg를 넣어도 다른 결과를 return한다.

 

2. STABLE

database를 수정할 수 없는 Function.

단일 구문 내의 모든 row에서 동일한 arg에 대해서는 같은 결과를 return한다.

Function을 이 카테고리로 설정하면 optimizer가 복수의 실행을 하나의 실행으로 최적화 해준다.

 

3. IMMUTABLE

database를 수정할 수 없는 Function

동일한 arg가 주어질 경우 영원히 같은 결과를 return한다.

상수 arg와 함께 호출 되면 optimizer가 function을 미리 평가할 수 있게 해준다.

 

 

Using Function with Trigger

CREATE
OR REPLACE FUNCTION set_updated_at () RETURNS TRIGGER AS $$
	BEGIN
		NEW.updated_at = CURRENT_TIMESTAMP;
		RETURN NEW;
	END;
$$ LANGUAGE plpgsql; -- Trigger를 사용해야 하기 때문에 sql이 아닌 plpgsql을 사용한다.

CREATE TRIGGER updated_at
BEFORE UPDATE -- OF를 사용해서 특정 column만을 위한 Trigger를 만들 수도 있다.
ON movies FOR EACH ROW
EXECUTE PROCEDURE set_updated_at();

 

 

✅ Procedure

---

Function과 Procedure는 비슷하면서도 다르다.

둘 다 SQL command 뭉치를 캡슐화 해준다는 점에서는 비슷하다.

 

Procedure는 Function과 같은 이름을 가질 수 없다.

 

Function과의 차이점

Function은 반드시 뭔가를 return해야 한다.

하지만 Procedure는 return을 할 수는 있지만 필수가 아니다.

 

Function은 DML command 안에서 호출된다.
Procedure는 DML command 안에서 호출하지 않는다.

CALL 뒤에 Procedure의 이름을 붙여서 호출한다.

Procedure 생성

CREATE OR REPLACE PROCEDURE set_zero_revenue() AS
$$
	UPDATE movies SET revenue = NULL WHERE revenue = 0;
$$
LANGUAGE SQL;

 

Procedure 호출

CALL set_zero_revenue ();

 

return값 있는 Procedure

CREATE OR REPLACE PROCEDURE hello_world_p(IN name TEXT, OUT greeting TEXT) AS
$$
	BEGIN
		greeting = 'Hello' || name;
	END;
$$
LANGUAGE plpgsql;

CALL hello_world_p ('nico', NULL);

output도 넣어서 호출해야 하기 때문에 NULL값을 넣어준다.

 

plpgsql(Procedural Language Postgre SQL)로 SQL 방식보다 Programming 방식으로 작성하기

CREATE
OR REPLACE PROCEDURE hello_world_i (IN name TEXT, IN lang TEXT, OUT greeting TEXT) AS $$
	DECLARE spanish_hello TEXT := 'hola';
	italian_hello TEXT := 'ciao';
	korean_hello TEXT := '안녕';
	BEGIN
		IF lang = 'korean' THEN
			greeting := korean_hello || ' ' || name || '!';
		ELSIF lang = 'italian' THEN
			greeting := italian_hello || ' ' || name || '!';
		ELSIF lang = 'spanish' THEN
			greeting := spanish_hello || ' ' || name || '!';
		ELSE
			greeting := 'hello ' || name || '!';
		END IF;
	END;
$$ LANGUAGE plpgsql;

CALL hello_world_i ('nico', 'spanish', NULL);

plpgsql을 사용할 때는 BEGIN과 END를 필수로 사용해야 한다. (Trigger를 사용해야 할 때도 plpgsql로 작성해야 한다.)