이 랩에서는 PostgreSQL 저장 함수를 개발하는 방법을 배웁니다. 기본 저장 함수 정의, 기본값이 있는 입력 매개변수 추가, 쿼리 내에서 함수 실행, 마지막으로 사용되지 않는 함수를 제거하여 데이터베이스를 정리하는 과정을 진행합니다.
이 단계에서는 PostgreSQL 에서 기본 저장 함수를 정의하는 방법을 배웁니다. 저장 함수는 특정 작업을 수행하는 재사용 가능한 코드 블록이며 데이터베이스 내에서 실행할 수 있습니다.
먼저 터미널을 열고 psql 명령줄 도구를 사용하여 PostgreSQL 데이터베이스에 연결합니다. 모든 데이터베이스 작업은 이 psql 셸 내에서 수행합니다.
sudo -u postgres psql이제 postgres=#와 같이 보이는 PostgreSQL 프롬프트가 표시됩니다.
이제 고정된 정수 값을 반환하는 get_total_products라는 함수를 생성합니다. 이 간단한 함수는 기본 구문을 이해하는 데 도움이 될 것입니다. psql 셸에서 다음 SQL 명령을 실행합니다.
CREATE FUNCTION get_total_products()
RETURNS INTEGER AS $$
BEGIN
RETURN 100;
END;
$$ LANGUAGE plpgsql;이 명령을 자세히 살펴보겠습니다.
CREATE FUNCTION get_total_products(): 이 구문은 입력 매개변수가 없는 get_total_products라는 새 함수를 정의합니다.RETURNS INTEGER: 이 구문은 함수가 INTEGER 데이터 유형의 단일 값을 반환함을 지정합니다.AS $$ ... $$: 달러로 따옴표로 묶인 문자열 $$는 함수 본문을 포함하는 데 사용됩니다. 이는 함수 코드 내의 작은따옴표 문제를 피하기 위해 PostgreSQL 에서 일반적으로 사용되는 관행입니다.BEGIN ... END;: 이 블록에는 함수의 실행 가능한 부분이 포함됩니다.RETURN 100;: 이것은 단순히 정수 100을 반환하는 함수 로직입니다.LANGUAGE plpgsql: 이 구문은 함수가 PostgreSQL 의 절차적 언어인 plpgsql로 작성되었음을 지정합니다.명령을 실행한 후 PostgreSQL 은 함수 생성을 확인합니다.
CREATE FUNCTION함수가 생성되었는지 확인하려면 SELECT 문에서 함수를 호출할 수 있습니다.
SELECT get_total_products();출력에는 함수에서 반환된 값이 표시됩니다.
get_total_products
--------------------
100
(1 row)이는 첫 번째 저장 함수가 올바르게 작동하고 있음을 확인합니다.
저장 함수는 입력 매개변수를 받을 수 있을 때 더 강력해집니다. 이 단계에서는 두 개의 숫자를 입력으로 받아 합계를 반환하는 새 함수를 생성합니다.
이전 단계에서 사용했던 psql 셸에 여전히 있는지 확인합니다. 이제 두 개의 정수 매개변수를 받는 add_numbers라는 함수를 생성합니다.
CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
RETURN a + b;
END;
$$ LANGUAGE plpgsql;구문은 이전 단계와 유사하며 함수 시그니처에 주요 차이점이 있습니다.
add_numbers(a INTEGER, b INTEGER): 이 구문은 함수를 호출할 때 제공해야 하는 INTEGER 유형의 두 매개변수 a와 b를 정의합니다.명령을 실행한 후 CREATE FUNCTION 확인 메시지가 표시됩니다.
이제 두 개의 숫자를 인수로 제공하여 함수를 테스트합니다.
SELECT add_numbers(15, 25);함수는 두 숫자를 더하고 결과를 반환합니다.
add_numbers
-------------
40
(1 row)기본 매개변수 값이 있는 함수를 사용할 수도 있습니다. 이제 인사말 메시지에 기본값이 있는 새 함수 greet_user를 생성합니다.
CREATE OR REPLACE FUNCTION greet_user(username VARCHAR, greeting VARCHAR DEFAULT 'Hello')
RETURNS TEXT AS $$
BEGIN
RETURN greeting || ', ' || username || '!';
END;
$$ LANGUAGE plpgsql;여기서 CREATE OR REPLACE는 함수가 이미 존재하는 경우 업데이트합니다. greeting 매개변수에는 기본값 'Hello'가 할당됩니다. || 연산자는 문자열 연결에 사용됩니다.
필수 username 매개변수만 제공하여 함수를 테스트합니다.
SELECT greet_user('Alex');함수는 기본 인사말을 사용합니다.
greet_user
------------------------
Hello, Alex!
(1 row)이제 사용자 지정 인사말을 제공하여 다시 호출합니다.
SELECT greet_user('Alex', 'Welcome');이제 출력에 사용자 지정 메시지가 표시됩니다.
greet_user
-----------------------
Welcome, Alex!
(1 row)저장 함수의 일반적인 용도는 테이블의 데이터에 대한 계산을 수행하는 것입니다. 이 단계에서는 테이블을 생성하고, 데이터를 채운 다음, 해당 테이블에 대한 쿼리에서 함수를 사용합니다.
먼저 제품 이름과 가격을 저장할 products라는 간단한 테이블을 생성합니다.
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
price NUMERIC(10, 2)
);CREATE TABLE 확인 메시지가 표시됩니다.
다음으로 products 테이블에 샘플 데이터를 삽입합니다.
INSERT INTO products (name, price) VALUES
('Laptop', 1200.00),
('Mouse', 25.50),
('Keyboard', 75.00);INSERT 0 3이 표시되어 세 개의 행이 삽입되었음을 나타냅니다.
이제 판매세가 포함된 가격을 계산하는 함수를 생성해 보겠습니다. 이 함수는 가격을 입력으로 받아 7% 세금이 포함된 가격을 반환합니다.
CREATE FUNCTION calculate_taxed_price(price NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
RETURN price * 1.07;
END;
$$ LANGUAGE plpgsql;함수를 생성한 후 products 테이블의 SELECT 쿼리에서 직접 사용할 수 있습니다. 이 쿼리는 각 제품에 대해 원래 가격과 세금 포함 가격을 표시합니다.
SELECT name, price, calculate_taxed_price(price) AS taxed_price FROM products;출력에는 각 행에 대한 함수 호출 결과가 표시됩니다.
name | price | taxed_price
----------+---------+-------------
Laptop | 1200.00 | 1284.0000
Mouse | 25.50 | 27.2850
Keyboard | 75.00 | 80.2500
(3 rows)이를 통해 비즈니스 로직을 함수로 캡슐화하고 데이터를 적용하는 방법을 보여줍니다.
더 이상 필요하지 않은 데이터베이스 객체는 제거하는 것이 좋습니다. 이 단계에서는 데이터베이스에서 저장 함수를 제거하거나 "삭제"하는 방법을 배웁니다. 첫 번째 단계에서 생성한 get_total_products 함수를 제거합니다.
먼저 데이터베이스의 함수 목록을 나열하여 get_total_products가 존재하는지 확인할 수 있습니다.
\dfget_total_products를 포함한 함수 목록이 표시됩니다.
함수를 제거하려면 DROP FUNCTION 명령을 사용합니다. 함수 이름을 지정해야 합니다. 함수에 매개변수가 있는 경우 해당 유형을 지정해야 하지만, get_total_products에는 매개변수가 없으므로 이름만 사용하면 됩니다.
DROP FUNCTION get_total_products();PostgreSQL 이 작업을 확인합니다.
DROP FUNCTION이제 \df로 함수 목록을 다시 나열하면 get_total_products가 목록에 더 이상 없는 것을 볼 수 있습니다.
함수가 오버로드된 경우 (즉, 동일한 이름이지만 매개변수가 다른 여러 함수) 인수의 유형을 지정하는 것도 중요합니다. 예를 들어, add_numbers 함수를 삭제하려면 정수 매개변수를 지정해야 합니다.
DROP FUNCTION add_numbers(INTEGER, INTEGER);마지막으로 환경을 정리하기 위해 이전 단계에서 생성한 products 테이블을 삭제합니다.
DROP TABLE products;이것으로 실습을 마칩니다. psql 셸을 종료하려면 \q를 입력하고 Enter 키를 누릅니다.
이 실습에서는 PostgreSQL 에서 저장 함수를 개발하는 기본 사항을 배웠습니다. 기본 함수를 생성하고, 매개변수 및 기본값으로 향상시키고, 테이블 데이터에 대한 쿼리에서 함수를 적용하고, 마지막으로 함수와 테이블을 제거하여 데이터베이스를 정리했습니다. 이러한 기술은 더 모듈화되고 효율적인 데이터베이스 솔루션을 만드는 데 필수적입니다.
