[Postgresql] 프로시저(Procedure) : 테이블 생성 프로시저를 만들어보자.

 

 

 

 

이번에 프로젝트를 하면서 테이블을 생성하는 프로시저를 만들 일이 있어서 한번 작성해본다.

 

 

Postgresql : 테이블 생성 및 삭제 프로시저 작성

 

샘플코드

CREATE OR REPLACE PROCEDURE 스키마.프로시저명()
 LANGUAGE plpgsql
AS $procedure$
	begin
		execute 'DROP TABLE IF EXISTS 스키마.테이블명 CASCADE';
		execute 'CREATE TABLE IF NOT EXISTE 스키마.테이블명 (
	              my_id varchar(20) NOT NULL,
	              my_pwd numeric(20) NOT NULL,
	              my_name varchar(20) NOT NULL
                )';
	END;
$procedure$
;

 

위는 진짜 간단하게 테이블을 CREATE하고 DROP시켜주는 프로시저 생성문이다. 어려울 것도 없다.

그냥 평소 하던대로 CREATE와 DROP을 해주면 되니..

 

근데 만약 테이블명이나, 스키마명 또는 속성값을 파라메터로 받아 사용하려면 조금 문제가 생긴다.

 

 

 

파라메터를 받아 DDL을 수행하는 프로시저 작성?

 

예를 들면 아래와 같은 프로시저가 있다고 생각해보자.

 

 

샘플코드

-- myTable : 테이블명
-- name : myTable 테이블에 있는 컬럼명
call mySchema.createTable('myTable', 'name');

 

밑의 쿼리문을 보면 알겠지만 여기서 mySchema.createTable 프로시저의

 

첫번째 파라메터는 Create하고자 하는 테이블명,

두번째 파라메터는 Create한 myTable의 name 컬럼을 Delete하기 위한 파라메터이다.

 

 

잘못된 샘플코드

CREATE OR REPLACE PROCEDURE mySchema.create_table(myTable text, name text)
 LANGUAGE plpgsql
AS $procedure$
	begin
		execute 'CREATE TABLE IF NOT EXISTS mySchema.' || myTable || '(
			my_id varchar(20) NOT NULL,
			my_pwd numeric(20) NOT NULL,
			my_name varchar(20) NOT NULL
		)';
        
		execute 'delete from mySchema.' || myTable || ' where name = ' || name;
	END;
$procedure$
;

 

대충 파라메터를 받아서 테이블을 CREATE하고 조건절 WHERE에 사용한다면 위와 같이 작성하는데

이렇게 하면 오류를 뱉어낸다.

 

이유는 파라메터로 받아온 저 친구들은 쌍따옴표(")와 홑따옴표(')로 묶여 있지 않기 때문이다.

 

여기서 테이블명으로 사용하는 파라메터에는 쌍따옴표(")를, 속성으로 사용하는 녀석은 홑따옴표(')로 묶어주면 된다

그럼 아래와 같이 쿼리문을 작성할 수 있다.

 

 

정상적인 샘플코드

CREATE OR REPLACE PROCEDURE mySchema.create_table(myTable text, name text)
 LANGUAGE plpgsql
AS $procedure$
	begin
		execute 'CREATE TABLE IF NOT EXISTS mySchema.' || quote_ident(myTable) || '(
			my_id varchar(20) NOT NULL,
			my_pwd numeric(20) NOT NULL,
			my_name varchar(20) NOT NULL
		)';
        
		execute 'delete from mySchema.' || quote_ident(myTable) || ' where name = ' || quote_literal(name);
	END;
$procedure$
;

 

 

이렇게 quote_ident함수와 quote_literal함수를 이용하면 간단히 쌍따옴표("), 홑따옴표(')를 붙일 수 있다.

관련하여 더 알고 싶은 사람은 아래 링크를 통해 참조하도록 하자.

 

 

[Query] 오라클/MySQL/Postgresql : 홑따옴표( ' ) 사용하는 방법

 

[Postgresql] quote_ident/ quote_literal 함수사용방법 : Postgresql에서 쌍따옴표/홑따옴표 사용하는 방법