[PostgreSQL] format function - 문자열 포맷 출력 및 동적 쿼리 생성 (ft. EXECUTE FORMAT)

PostgreSQL에서 제공하는 format 함수에 대해 알아보자.

 

▷ 구문

FORMAT(format_string [, format_arg [, ...] ])

- format_string

 format() 함수 결과의 형식을 지정하는 문자열이며, %[position][flags][width]type 의 형식을 가지고 있다.

 position : 숫자형식으로 표현하며 가져올 인수의 인덱스이다. 생략이 가능하며 생략 시 format_arg에 지정된 순서대로 할당된다.

 flags : width 표현에 사용된 숫자에 -를 붙일 경우 왼쪽 정렬이고, 생략 시 오른쪽 정렬이다.

 width : 문자열을 출력할 공간을 지정하며, 결과 문자열을 채우는 데 필요한 공백으로 왼쪽 또는 오른쪽을 채울 수 있다. 다음 함수 인수를 너비로 사용하려면 별표(*) 또는 n번째 함수 인수를 너비로 사용하려면 *n$ 형식으로 사용된다.

 type : 필수 값이며 출력형식 변환 유형을 지정한다.

      ●  s  - 인수 값을 문자열로 형식화한다. NULL은 빈 문자열로 처리된다.

      ●  I  - 인수 값을 SQL 식별자로 취급한다. NULL일 경우 오류가 발생한다.

      ●  L  - 인수 값을 SQL 리터럴로 취급한다. NULL을 그대로 출력한다.

 보통 동적 SQL문을 구성 하기 위해서 I 와 L을 자주 사용하고는 한다.

 

- format_arg

 format() 함수에 전달하는 인수들을 의미한다.

---

이제 구문에 대해 알아봤으니 쉽게 접근하기 위해 예제를 통해 알아보도록 하자.

 

▷ 예제1) 단순 형식의 format() 함수 기본 사용

-- type : s
-- result : Hello, mine-it-record
SELECT FORMAT('Hello, %s','mine-it-record');
SELECT FORMAT('Hello, %s-%s-%s','mine','it','record');

-- result : full_name (last_name, first_name)
SELECT FORMAT('%s, %s',last_name, first_name) full_name 
FROM customer;

구문에서 나온것 처럼 필수값인 type만 지정해두고 type은 그중에서 s를 지정해 문자열을 인수로 넘겨준것이다.

'Hello, %s' 와 'Hello, %s-%s-%s' 부분이 format_string 즉, 출력되는 영역이고

나머지 뒤에 선언해놓은 문자열들은 format_arg 즉, %s에 전달할 인수들을 의미한다.

 

-- type : I
-- ALTER sequence record.cm.seq_mine_sn OWNER TO mine;
SELECT FORMAT(
  'ALTER sequence %I.%I.%I OWNER TO %I;',
  sequence_catalog,
  sequence_schema,
  sequence_name,
  'mine'  -- or another just put it in quotes
)
FROM information_schema.sequences

사실 %s와 %I의 큰 차이는 잘 모르겠으나 단순 select 구문에서 사용할 결과값이 아닌,

insert, alter 등의 쿼리문을 생성할때는 I를 많이 사용하는것 같다.

 

-- type : L
-- result : INSERT INTO level VALUES('HARD')
SELECT format('INSERT INTO %I VALUES(%L)', 'level', 'HARD');

-- compare result
-- result : INSERT INTO level VALUES(HARD)
SELECT format('INSERT INTO %I VALUES(%s)', 'level', 'HARD');

-- result : INSERT INTO level VALUES("HARD")
SELECT format('INSERT INTO %I VALUES(%I)', 'level', 'HARD');

-- result : INSERT INTO level VALUES('HARD')
SELECT format('INSERT INTO %I VALUES(%L)', 'level', 'HARD');

-- result : INSERT INTO "Foo bar" VALUES("O'Reilly")
SELECT format('INSERT INTO %I VALUES(%I)', 'Foo bar', E'O\'Reilly');

-- result : INSERT INTO "Foo bar" VALUES('O''Reilly')
SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');

확실히 결과를 보면 %s는 values안에는 사용할 수 없을것같고 I나 L을 사용하면 될것같은데

$L 과 $I 사이에서도 표현되는 부분에 대해서 확실히 다른 모습을 볼 수 있다.

 

▷ 예제2) type 속성의 width 와 -flag 옵션 사용

-- result : |       foo|
SELECT format('|%10s|', 'foo');

-- result : |foo       |
SELECT format('|%-10s|', 'foo');

-- use width option : *
-- result : |       foo|
SELECT format('|%*s|', 10, 'foo');

-- result : |foo       |
SELECT format('|%*s|', -10, 'foo');

-- result : |foo       |
SELECT format('|%-*s|', 10, 'foo');

-- result : |foo       |
SELECT format('|%-*s|', -10, 'foo');

-- use width position options
-- result : |       bar|
SELECT format('|%*2$s|', 'foo', 10, 'bar');

-- use postion and width position options
-- result : |       foo|
SELECT format('|%1$*2$s|', 'foo', 10, 'bar');

위 구문에서 설명한것처럼 -flag 옵션은 width의 숫자 앞에 - 가 붙어야하는데 width 옵션 중에서 *을 사용함으로써 인수를 받아 사용한 것이다.

그리고 width 옵션중 n번째 인수를 사용하는 방법에 대해 하단 예제에 나와있다.

 

▷ 예제3) position 옵션 사용

-- result : Testing three, two, one
SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three');

-- result : Testing three, two, three
SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');

position 옵션을 사용할 경우 위치를 지정해주는데 두번째 쿼리문처럼 생략해줄 경우 그 위치에 맞는 인수가 할당된다.

---

여기까지 format 함수에 대해 알아봤는데, 이 format 함수는 function에서 많이 사용되고는 한다.

 

동적 쿼리를 만들어 자동으로 실행되게끔 하는 function을 만들어 사용할때 사용되는데,

사용법은 단순히 위 예제들처럼 FORMAT 함수를 사용하여 쿼리문을 만들고 앞에 EXECUTE를 붙여주기만 하면되기 때문에 아래 구문만 보고 마치도록 하겠다.

 

▷ 구문

EXECUTE FORMAT(format_string [, format_arg [, ...] ])

---

참고 : https://www.postgresql.org/docs/10/functions-string.html

9.4. String Functions and Operators