[postgreSQL] PostgreSQL 함수. Function

PostgreSQL 함수 특징

모든 종류의 SQL 명령어는 함수로 정의할 수 있다.

PostgreSQL는 프로시저와는 다르게 트랜잭션 제어 명령( ex. commit, savepoint) 및 vacutaion 등의 일부 유틸리티 명령은 사용할 수 없다.

Return값이 없는 경우 void로 Return값을 정의할 수 있지만 이런 경우에는 프로시저를 사용한다.

프러시저가 SQL 표준이며, 프로시져는 transaction 컨트롤 등 추가적인 기능을 제공하기 때문이다.

 

PostgreSQL 함수 구조

CREATE [OR REPLACE] FUNCTION function_name (arguments)
RETURNS return_datatype AS $$
DECLARE
    -- 변수 선언
BEGIN
    -- 함수 로직
END;
$$ LANGUAGE plpgsql;

CREATE [OR REPLACE] FUNCTION	함수를 생성합니다. [OR REPLACE] 는 기존 함수를 업데이트합니다.
function_name	함수의 이름을 지정합니다.
arguments	함수의 입력 인수를 지정합니다.
RETURNS return_datatype	함수의 반환 데이터 유형을 지정합니다.
DECLARE	함수에서 사용할 변수를 선언합니다.
BEGIN, END	함수의 로직을 포함합니다.
$$ LANGUAGE plpgsql	함수의 언어를 지정합니다. 이 경우 plpgsql입니다.

 

PostgreSQL 함수 파라미터 특징

1. 이름 기반 인자 참조: 함수 내에서 이름으로 인자를 참조할 수 있다.

CREATE FUNCTION calculate_tax(amount NUMERIC, tax_rate NUMERIC) RETURNS NUMERIC AS $$
BEGIN
    RETURN amount * tax_rate;
END;
$$ LANGUAGE plpgsql;

여기서 amount와 tax_rate는 함수의 인자로 사용되고, 함수 본문에서 이들을 참조하여 계산에 활용한다.

2. 우선순위 규칙: 함수 인자의 이름이 SQL 테이블의 칼럼과 일치할 경우, 칼럼이 인자보다 우선순위를 가진다.

CREATE FUNCTION find_employee(name VARCHAR) RETURNS SETOF employee AS $$
BEGIN
    RETURN QUERY SELECT * FROM employee WHERE employee.name = find_employee.name;
END;
$$ LANGUAGE plpgsql;

여기서 name 인자가 함수 내에서 employee 테이블의 name 칼럼과 충돌할 경우, 테이블의 칼럼이 우선권을 가진다.

3. 함수 자체의 이름으로 인자 명시: 함수 인자의 이름을 함수 자체의 이름과 함께 명시하여 충돌을 방지할 수 있다.

CREATE FUNCTION calculate_discount(calculate_discount.amount NUMERIC, rate NUMERIC) RETURNS NUMERIC AS $$
BEGIN
    RETURN calculate_discount.amount * rate;
END;
$$ LANGUAGE plpgsql;

여기서 calculate_discount.amount는 함수 calculate_discount의 인자를 명시적으로 나타낸 것이다.

이렇게 테이블의 컬럼명과 인자 이름의 충돌을 피할 수 있다.

4. 숫자 형태의 인자 접근법:  $n 형태의 숫자를 사용하여 함수 인자에 접근할 수 있다.

CREATE FUNCTION greet(greeting TEXT, name TEXT) RETURNS TEXT AS $$
BEGIN
    RETURN greeting || ' ' || $2;
END;
$$ LANGUAGE plpgsql;

$2는 두 번째 인자인 name을 가리킨다.

5. 식별자가 아닌 데이터 값 사용: SQL 함수의 인자는 식별자가 아닌 데이터 값으로만 사용할 수 있다.

CREATE FUNCTION calculate_area(length NUMERIC, width NUMERIC) RETURNS NUMERIC AS $$
BEGIN
	-- 가능
	INSERT INTO calculate_data VALUES ($1*$2);
	-- 불가
	INSERT INTO $1 VALUES (42);
    RETURN length * width;
END;
$$ LANGUAGE plpgsql;

 

PostgreSQL 함수 종류와 예

1. 기본타입의 인자를 받고 기본타입을 반환하는 기본 함수

CREATE FUNCTION add_numbers(x INT, y INT) RETURNS INT AS $$
BEGIN
    RETURN x + y;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION multiply_numbers(x INT, y INT) RETURNS INT AS $$
BEGIN
    RETURN $1 * $2;
END;
$$ LANGUAGE plpgsql;

---
SELECT add_numbers(1, 2) AS answer1;
SELECT multiply_numbers(1, 2) AS answer2;

2. 함수의 인자명과 테이블 칼럼명이 일치하는 경우, 함수 인자명을 테이블 칼럼과 동일하게 사용하는 형태

-- 예시 테이블 생성
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary NUMERIC
);

-- 예시 데이터 삽입
INSERT INTO employees (name, salary) VALUES ('Alice', 50000);
INSERT INTO employees (name, salary) VALUES ('Bob', 60000);

-- 함수 정의: 이름을 받아 해당하는 직원의 월급 조회
CREATE FUNCTION get_salary(name VARCHAR) RETURNS NUMERIC AS $$
BEGIN
    RETURN (SELECT salary FROM employees WHERE employees.name = get_salary.name);
END;
$$ LANGUAGE plpgsql;

 

3. 다양한 형태로 Return을 받을 수 있는 함수

CREATE TYPE employee AS (
    name VARCHAR(100),
    salary NUMERIC,
    age INT,
    cubicle POINT
);

CREATE FUNCTION create_employee() RETURNS employee AS $$
BEGIN
    RETURN ROW('John Doe', 50000.0, 30, '(3,4)')::employee;
END;
$$ LANGUAGE plpgsql;

-- 1. row 자체로 select
SELECT create_employee();

-- 2. 테이블 형태로 select
SELECT * FROM create_employee();

-- 3. 속성별 select
SELECT (create_employee()).name;

4. 출력 매개변수를 받아 Retrun하는 함수

CREATE FUNCTION calculate_discount(total_amount NUMERIC, OUT discounted_amount NUMERIC) RETURNS VOID AS $$
BEGIN
    IF total_amount > 100 THEN
        discounted_amount := total_amount * 0.9; -- 10% discount
    ELSE
        discounted_amount := total_amount;
    END IF;
END;
$$ LANGUAGE plpgsql;

--- 호출
DO $$
DECLARE
    total NUMERIC := 120;
    discount NUMERIC;
BEGIN
    PERFORM calculate_discount(total, OUT discount);
    RAISE NOTICE 'Discounted amount: %', discount;
END;
$$;

5. Default 값을 인자로 가지는 Function

 인자없이 호출하면 default값을 인자로 사용. 인자와 함께 호출하면 인자 값을 인자로 사용한다.

CREATE FUNCTION greet(name TEXT DEFAULT 'Guest') RETURNS TEXT AS $$
BEGIN
    RETURN 'Hello, ' || name || '!';
END;
$$ LANGUAGE plpgsql;

---
SELECT greet('John'); -- 출력: Hello, John!
SELECT greet();       -- 출력: Hello, Guest!

6. Return 값을 일반적인 테이블 열과 동일하게 사용 하는 경우

CREATE TABLE products (
    product_id INT,
    product_name TEXT,
    price NUMERIC
);

INSERT INTO products VALUES (1, 'Apple', 1.99);
INSERT INTO products VALUES (2, 'Banana', 0.99);
INSERT INTO products VALUES (3, 'Orange', 2.49);

CREATE FUNCTION get_products_below_price(target_price NUMERIC) RETURNS products AS $$
    SELECT * FROM products WHERE price < target_price;
$$ LANGUAGE SQL;

SELECT *, price * 0.9 AS discounted_price FROM get_products_below_price(2) AS t1;

7. SETOF를 사용하여 함수가 여러 개의 행을 반환하는 예

CREATE TABLE employees (
    employee_id INT,
    employee_name TEXT,
    department TEXT
);

INSERT INTO employees VALUES (1, 'Alice', 'HR');
INSERT INTO employees VALUES (2, 'Bob', 'IT');
INSERT INTO employees VALUES (3, 'Charlie', 'Marketing');

CREATE FUNCTION get_employees_in_department(dep TEXT) RETURNS SETOF employees AS $$
    SELECT * FROM employees WHERE department = dep;
$$ LANGUAGE SQL;


---
SELECT * FROM get_employees_in_department('HR');=

8. table을 return하는 함수

 - OUT, INOUT 인자를 사용할 수 없고, 모든 결과 칼럼을 TABLE에 정의해야 한다.

CREATE FUNCTION get_employee_info_table(employee_id INT) RETURNS TABLE(name VARCHAR(100), salary NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT e.name, e.salary
    FROM employees e
    WHERE e.id = employee_id;
END;
$$ LANGUAGE plpgsql;

9. anyarray 인자를 활용한 함수

 - 'anyarray'는 PostgreSQL에서 다양한 유형의 배열을 대표하는 데이터 유형아더. 이 데이터 유형은 함수의 인자로 배열을 전달할 때 사용된다. 'anyarray'는 임의의 배열을 나타내며, 어떤 유형의 데이터든 포함할 수 있다.

'anyarray' 데이터 유형은 함수 정의에서 배열 형태의 인자를 지원하는 유용한 방법이다. 이를 통해 함수는 동일한 구조의 배열이지만 유형이 다른 여러 유형의 데이터를 처리할 수 있다.

CREATE OR REPLACE FUNCTION array_sum(arr anyarray) RETURNS NUMERIC AS $$
DECLARE
    total NUMERIC := 0;
BEGIN
    FOR i IN 1..array_length(arr, 1) LOOP
        total := total + arr[i];
    END LOOP;
    RETURN total;
END;
$$ LANGUAGE plpgsql;