CodeGym /행동 /SQL SELF /EXECUTE로 프로시저 중첩 호출하기: SQL 코드 동적으로 실행하기

EXECUTE로 프로시저 중첩 호출하기: SQL 코드 동적으로 실행하기

SQL SELF

레벨 53 , 레슨 4

사용 가능

실습에 들어가기 전에, 먼저 동적 SQL이 뭔지부터 짚고 넘어가자! 예를 들어, 파라미터로 전달받은 고유한 이름으로 테이블을 만들어야 한다고 해보자. 아니면, 프로그램 실행 중에 이름이 정해지는 테이블에 쿼리를 날려야 할 수도 있지. 이런 경우엔 그냥 정적인 SQL로는 부족해 — 바로 이럴 때 동적 실행이 필요해.

PL/pgSQL에서는 EXECUTE 명령어로 문자열 형태의 SQL 쿼리를 실행할 수 있어. 이걸로 파라미터에 따라 달라지는 SQL 코드를 "즉석에서" 만들어서 실행할 수 있지.

동적 SQL이 유용한 이유는 이런 거야:

유연성: 입력값에 따라 쿼리를 동적으로 만들 수 있어. 예를 들어, 미리 이름을 알 수 없는 테이블이나 컬럼에 대해 작업할 때 쓸 수 있지.
자동화: 고유한 이름으로 테이블이나 인덱스를 자동으로 만들 수 있어.
범용성: 데이터 구조가 달라도 프로시저를 다시 짤 필요 없이 쓸 수 있어.

실제 예시: 예를 들어, 너가 분석 시스템을 개발 중인데, 새 클라이언트마다 데이터 저장용 테이블을 따로 만들어야 한다고 해보자. 이런 것도 EXECUTE로 자동화할 수 있어.

`EXECUTE` 문법

EXECUTE로 동적 SQL을 쓰는 방법은 이래:

EXECUTE 'SQL-문자열';

간단한 쿼리 예시:

DO $$
BEGIN
  EXECUTE 'CREATE TABLE test_table (id SERIAL PRIMARY KEY, name TEXT)';
END $$;

이 코드 블록은 test_table 테이블을 만들어. 간단하지? 이제 좀 더 복잡한 상황도 알아보자.

`EXECUTE` 활용 예시

1. 동적 이름으로 테이블 만들기

예를 들어, 오늘 날짜에 따라 이름이 달라지는 테이블을 만들어야 한다고 해보자. 이렇게 할 수 있어:

DO $$
DECLARE
  table_name TEXT;
BEGIN
  -- 테이블 이름 생성하기
  table_name := 'report_' || to_char(CURRENT_DATE, 'YYYYMMDD');

  -- 동적 이름으로 테이블 만들기
  EXECUTE 'CREATE TABLE ' || table_name || ' (id SERIAL PRIMARY KEY, data TEXT)';

  -- 확인용 메시지 출력
  RAISE NOTICE '테이블 % 성공적으로 생성됨', table_name;
END $$;

여기서 테이블 이름은 오늘 날짜로 만들어지고, 최종 SQL 문자열이 EXECUTE로 전달돼.

2. 동적 파라미터로 쿼리 실행하기

이번엔, 테이블 이름을 파라미터로 받아서 데이터를 뽑아야 한다고 해보자. 이런 함수로 만들 수 있어:

CREATE OR REPLACE FUNCTION get_data_from_table(table_name TEXT)
RETURNS TABLE(id INTEGER, name TEXT) AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT id, name FROM ' || table_name || ' WHERE id < 10';
END $$ LANGUAGE plpgsql;

함수 호출 예시:

SELECT * FROM get_data_from_table('employees');

이런 방식은 동적 리포트 시스템 같은 범용 유틸리티 만들 때 딱이야.

동적 SQL의 문제점과 한계

동적 SQL 실행은 자유도가 높지만, 그만큼 책임도 커. 이런 부분에서 문제가 생길 수 있어:

SQL 인젝션: 문자열 파라미터를 그냥 쿼리에 넣으면, 악의적인 사용자가 임의의 SQL 코드를 실행할 수도 있어.

취약한 코드 예시:
```
EXECUTE 'SELECT * FROM users WHERE name = ''' || user_input || '''';
```
만약 user_input에 '; DROP TABLE users; -- 같은 값이 들어오면, users 테이블이 날아갈 수도 있어.
디버깅 어려움: 동적 코드는 실행 중에 쿼리가 만들어지니까, 분석이나 디버깅이 더 힘들어.
성능 저하: 동적 쿼리는 PostgreSQL의 실행 계획 캐시를 제대로 못 써서, 성능이 떨어질 수 있어.

SQL 인젝션 방어법

SQL 인젝션 공격을 막으려면, 문자열을 그냥 이어붙이지 말고 동적 쿼리에서 파라미터화를 써야 해. PL/pgSQL에서는 quote_literal()로 문자열 파라미터를, quote_ident()로 테이블/컬럼 이름 같은 식별자를 안전하게 처리할 수 있어.

안전한 코드 예시:

DO $$
DECLARE
  table_name TEXT;
  user_input TEXT := 'John';
BEGIN
  table_name := 'employees';

  EXECUTE 'SELECT * FROM ' || quote_ident(table_name) ||
          ' WHERE name = ' || quote_literal(user_input);
END $$;

실전: 테이블 동적 업데이트

아래는 파라미터로 받은 이름의 테이블에서 값을 업데이트하는 프로시저 예시야:

CREATE OR REPLACE FUNCTION update_table_data(table_name TEXT, id_value INT, new_data TEXT)
RETURNS VOID AS $$
BEGIN
  EXECUTE 'UPDATE ' || quote_ident(table_name) ||
          ' SET data = ' || quote_literal(new_data) ||
          ' WHERE id = ' || id_value;
END $$ LANGUAGE plpgsql;

함수 호출 예시:

SELECT update_table_data('test_table', 1, '업데이트된 값');

예시: 클라이언트용 리포트 만들기

이번엔, 클라이언트별로 주문 내역을 관리하고, 각 클라이언트마다 리포트 테이블을 자동으로 만드는 과정을 예로 들어볼게.

CREATE OR REPLACE FUNCTION create_client_report(client_id INT)
RETURNS VOID AS $$
DECLARE
  table_name TEXT;
BEGIN
  -- 리포트 테이블 이름 만들기
  table_name := 'client_report_' || client_id;

  -- 리포트용 테이블 생성
  EXECUTE 'CREATE TABLE ' || quote_ident(table_name) || ' (order_id INT, amount NUMERIC)';

  -- 데이터 채우기
  EXECUTE 'INSERT INTO ' || quote_ident(table_name) ||
          ' SELECT order_id, amount FROM orders WHERE client_id = ' || client_id;

  RAISE NOTICE '클라이언트 % 리포트 생성됨: 테이블 %', client_id, table_name;
END $$ LANGUAGE plpgsql;

EXECUTE로 동적 SQL을 쓰면 PL/pgSQL에서 자동화랑 유연성을 엄청 높일 수 있어. 하지만 SQL 인젝션 위험도 있으니까 항상 조심해야 해. 쿼리를 안전하게 만들고 싶으면 quote_ident()랑 quote_literal()을 꼭 써!

다음 강의에서는 데이터 검증, 레코드 업데이트, 작업 로그 남기기 등 복합 프로시저 만드는 걸 더 깊게 다룰 거야. 동적 쿼리 작업이 이런 실전 과제의 핵심이 될 테니까, 미리 연습해두면 좋아!