[SQL] Functions and Procedures

자주 사용하는 작업을 수행하는 SQL 구문을 세트로 capsulize -> 나중에 필요할 때 호출해서 사용가능! 

코드를 반복해서 작성할 필요가 없다 

 

Function: 결과값을 반환하는 경우 

Procedure: 결과값 반환 없이 데이터 변경을 수행하는 경우 

---

Function 

- 연산 후 하나의 값(value)을 반환

- 데이터베이스 값을 변경할 필요가 없는 경우 

- SELECT 구문 내에서 사용 가능 

ex) 고객의 나이를 계산하는 함수 

-- 함수 만들기 
CREATE FUNCTION hello_world()   -- 'hello_world'라는 함수 생성 
RETURNS TEXT AS 
$$
  SELECT 'hello world';         -- 'hello world' 문자열을 반환
$$ 
LANGUAGE SQL;                   -- SQL 언어를 사용 


-- 함수 호출하기 
SELECT hello_world();
SELECT title, hello_world() FROM movies;

-- 함수 만들기 
CREATE OR REPLACE FUNCTION hello_world(text, text)   -- 'hello_world'라는 함수 생성 
RETURNS TEXT AS 
$$ 
  SELECT 'hello ' || $1 || ' and ' || $2;            -- 'hello world' 문자열을 반환
$$ 
LANGUAGE SQL;                                        -- SQL 언어를 사용


-- 함수 호출하기 
SELECT title, hello_world('dodo', 'didi') FROM movies;

 

 

 

영화가 hit / flop 인지 계산하는 function 

-- 영화의 hit/flop 여부 계산 
SELECT title,
  CASE 
    WHEN revenue > budget THEN 'HIT'
    WHEN revenue < budget THEN 'FLOP'
    ELSE 'N/A'
  END
FROM movies;


-- 위 연산을 함수로 만들기 
CREATE OR REPLACE FUNCTION is_hit_or_flop(movie movies)  -- movies 테이블의 모든 행을 입력값으로 
  RETURNS TEXT AS 
  $$                                                     -- 함수의 본문을 감싸는 PostgresSQL 문법 
    SELECT CASE 
      WHEN movie.revenue > movie.budget THEN 'HIT'
      WHEN movie.revenue < movie.budget THEN 'FLOP'
      ELSE 'N/A'
    END
  $$
  LANGUAGE SQL;
  
  
  SELECT title, is_hit_or_flop(movies.*)
    FROM movies;

DROP FUNCTION is_hit_or_flop(movies);

CREATE OR REPLACE FUNCTION is_hit_or_flop(movie movies)  -- movies 테이블의 모든 행을 입력값으로 
  RETURNS TABLE (hit_or_flop text, other_thing numeric) AS -- 두 개의 값을 테이블 형식으로 반환 
  $$                                                     -- 함수의 본문을 감싸는 PostgresSQL 문법 
    SELECT CASE 
      WHEN movie.revenue > movie.budget THEN 'HIT'
      WHEN movie.revenue < movie.budget THEN 'FLOP'
      ELSE 'N/A'
    END, 11111;
  $$
  LANGUAGE SQL;

SELECT title, (is_hit_or_flop(movies.*)).*    -- 두 개의 값을 하나의 튜플이 아닌 두 개의 칼럼으로 
    FROM movies;

 

 

SQL Function의 변동성(Volatility) 

1) immutable(불변): 동일한 입력에 대해 항상 같은 결과값을 반환

2) stable(안정적): 같은 쿼리 내에서는 동일한 값을 반환 / transaction이 바뀌면 값이 바뀔 수 있음 

3) volatile(변동가능): 동일한 입력값이어도 실행할 때마다 다른 결과값을 반환할 수 있음 

 

업데이트 될 때 column이 갱신되지 않는 문제 해결하기 

 

Trigger

: 특정 이벤트(INSERT, UPDATE, DELETE 등)가 발생할 때 자동으로 실행 

  즉, Trigger는 Trigger Function을 실행한다(트리거 함수를 호출) 

CREATE OR REPLACE FUNCTION set_updated_at()   -- 트리거 함수 생성 
  RETURNS TRIGGER AS 
  $$
    BEGIN
      NEW.updated_at = CURRENT_TIMESTAMP;
      RETURN NEW;
    END;
  $$
  LANGUAGE plpgsql; 


CREATE TRIGGER updated_at    -- 트리거 생성 
  BEFORE UPDATE              -- BEFORE UPDATE의 경우, set_updated_at() 함수 실행  
  ON movies
  FOR EACH ROW EXECUTE PROCEDURE set_updated_at();  -- movies 테이블을 업데이트 할 때, 각 row에 대해 이 함수를 호출

---

Procedure 

- 여러 개의 SQL 구문을 실행해야 하는 경우 

- 데이터를 삽입 / 수정 / 삭제해야하는 경우 

- transaction 처리가 필요한 경우 

ex) 주문을 처리하고 재고를 업데이트 

-- 모든 revenue 값을 가져오는 procedure 만들기 
CREATE PROCEDURE set_zero_revenue() AS     -- 프로시저 생성 
  $$
    UPDATE movies SET revenue = NULL WHERE revenue = 0;  -- revenue가 0이면 값을 NULL로 변경 
  $$
  LANGUAGE SQL;
    
CALL set_zero_revenue();

SELECT * FROM movies;

 

---

Python Extension 

PostgreSQL 17.4버전에서 python extension 설치하는 방법(윈도우) 

1) 관리자버전으로 파워쉘 열기 

 

2) PostgresSQL이 설치된 경로로 이동 

cd "C:\Program Files\PostgreSQL\17\bin"

 

3) psql 실행 

.\psql -U postgres

 

4) 비밀번호 입력 후 PostgresSQL 접속 

 

5) PL/Python 확장 활성화

CREATE EXTENSION plpython3u;