PostgreSQL에서 서버 프로그래밍의 일환으로 PL/pgSQL, PL/Tcl, PL/Perl, PL/Python 등 여러 프로그래밍 언어를 사용하여 만들 수 있다. 함수(FUNCION)는 다양한 작업을 자동화하거나 간단한 데이터 처리를 수행할 수 있으며 그중 기본 설치되어 있는 PL/pgSQL 으로 간단한 예시를 정리해본다.
간단한 FUNCTION 만들기
이 함수는 단순히 “Hello, World!” 문자열을 반환한다.
CREATE OR REPLACE FUNCTION hello_world()
RETURNS text AS $$
BEGIN
RETURN 'Hello, World!';
END;
$$ LANGUAGE plpgsql;
함수(FUNCION)을 사용하는 방법은 아래처럼 select 문으로 바로 호출하면 된다.
SELECT hello_world();
/* 출력결과 */
hello_world
-------------
Hello, World!
두 개의 숫자를 받아서 합을 반환하는 함수이다.
CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
RETURN a + b;
END;
$$ LANGUAGE plpgsql;
실행결과이다.
select add_numbers(10,50);
/* 출력결과 */
add_numbers
-----------
60
그냥 select 로 할 수도 있지만, 테이블 명칭과 컬럼명칭을 동시에 줄 수도 있다.
select tab_name.col_name
from add_numbers(10,50) tab_name(col_name);
/* 출력결과 */
col_name
--------
60
IN 및 OUT 파라미터를 사용하는 함수는 입력 값을 받아서 처리한 후, 결과 값을 반환할 수 있다. OUT 파라미터는 반환 값으로 사용되며, 함수에서 명시적으로 RETURN을 호출하지 않아도 자동으로 결과가 반환된다. 아래는 IN과 OUT 파라미터를 사용하여 두 숫자의 합과 차를 계산하는 간단한 함수 예제이다.
CREATE OR REPLACE FUNCTION calculate_sum_and_diff(
IN num1 INTEGER,
IN num2 INTEGER,
OUT sum_result INTEGER,
OUT diff_result INTEGER
)
AS $$
BEGIN
sum_result := num1 + num2;
diff_result := num1 - num2;
END;
$$ LANGUAGE plpgsql;
이 함수를 호출하면, 두 숫자의 합(sum_result)과 차(diff_result)가 반환된다.
SELECT * FROM calculate_sum_and_diff(10, 5);
/* 출력결과 */
sum_result|diff_result
----------+-----------
15| 5
RETURNS TABLE
RETURNS TABLE 로 쿼리의 결과(레코드)를 반환할수 있다. 특징은 테이블의 구조를 지정하여, 결과를 반환할 수 있는 것인데, 기존과 동일한 구조로 리턴할 거라면, 조금은 귀찮은 리턴방식이고, 테이블간 JOIN 하여 조회하는 경우에 좋을거 같다. 우선 테스트를 위해 테이블을 하나 만들어본다.
-- employees 테이블 예시
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name TEXT,
position TEXT,
salary NUMERIC
);
INSERT INTO employees (name, position, salary) VALUES
('Alice', 'Manager', 70000),
('Bob', 'Manager', 75000),
('Charlie', 'Engineer', 60000);
RETURNS TABLE 을 이용하여 직원 정보를 리턴하는 함수를 만들었다.
CREATE OR REPLACE FUNCTION get_employee_info(employee_id INTEGER)
RETURNS TABLE (id INTEGER, name TEXT, position TEXT, salary NUMERIC) AS $$
BEGIN
RETURN QUERY
SELECT id, name, position, salary
FROM employees
WHERE id = employee_id;
END;
$$ LANGUAGE plpgsql;
오류가 발생했다. position이라는 컬럼 이름이 PostgreSQL에서 예약어이기 때문에 발생하는 문제이다.
select * from get_employee_info(1)
SQL Error [42601]: ERROR: syntax error at or near "position"
Position: 105
이 문제를 해결하기 위해서는 해당 컬럼 이름을 쿼리에서 사용할 때 따옴표로 감싸야 한다. PostgreSQL에서는 예약어로 인한 충돌을 피하기 위해 컬럼 이름을 쿼리에서 “로 감싸서 처리할 수 있다.
CREATE OR REPLACE FUNCTION get_employee_info(employee_id INTEGER)
RETURNS TABLE (id INTEGER, name TEXT, "position" TEXT, salary NUMERIC) AS $$
BEGIN
RETURN QUERY
SELECT id, name, position, salary
FROM employees
WHERE id = employee_id;
END;
$$ LANGUAGE plpgsql;
또 오류가 발생했다. 이건 PostgreSQL에서 PL/pgSQL 함수 내에서 id와 같은 변수나 컬럼 이름이 혼동될 때 발생하는 오류이다.
select * from get_employee_info(1)
SQL Error [42702]: ERROR: column reference "id" is ambiguous
Detail: It could refer to either a PL/pgSQL variable or a table column.
Where: PL/pgSQL function get_employee_info(integer) line 4 at RETURN QUERY
id는 테이블의 컬럼 이름일 수 있고, 동시에 함수의 지역 변수로 사용될 수도 있기 때문에 발생하는 오류라, 이 문제를 해결하려면 테이블의 컬럼과 변수의 이름이 충돌하지 않도록 해야 한다. 쿼리에 테이블 이름을 명시적으로 지정하여 충돌나지 않도록 수정했다.
CREATE OR REPLACE FUNCTION get_employee_info(employee_id INTEGER)
RETURNS TABLE (id INTEGER, name TEXT, "position" TEXT, salary NUMERIC) AS $$
BEGIN
-- 직원 정보를 반환
RETURN QUERY
SELECT e.id, e.name, e."position", e.salary
FROM employees e
WHERE e.id = employee_id;
END;
$$ LANGUAGE plpgsql;
드디어 실행이 되었다.
select * from get_employee_info(1)
id|name |position|salary
--+-----+--------+------
1|Alice|Manager | 70000
RETURNS SETOF
쿼리결과를 리턴할때, SETOF 와 RETURN QUERY 을 사용하서도 해결할 수 있다. join 하지 않고, 단일 테이블을 조회하는 거라면, 이 방법이 더 좋은거 같다.
CREATE OR REPLACE FUNCTION get_employees_by_position(job_position TEXT)
RETURNS SETOF employees AS $$
BEGIN
-- 직원 정보를 여러 행 반환
RETURN QUERY
SELECT *
FROM employees
WHERE position = job_position;
END;
$$ LANGUAGE plpgsql;
위에서 여러 문제를 겪어왔는데, 여기서는 바로 테이블의 정보를 이용해 받다보니, 테이블에 별도의 alias 를 지정할 필요도 없다.
SELECT * FROM get_employees_by_position('Manager');
id|name |position|salary
--+-----+--------+------
1|Alice|Manager | 70000
2|Bob |Manager | 75000