주 콘텐츠로 건너뛰기

검색

Welcome to Tibero GTS!

[OpenSQL] PL/pgSQL 사용 가이드

문서유형ㅣ기술정보

분야ㅣApp 개발

적용제품버전ㅣOpenSQL 3

문서번호ㅣODETI001

본 문서는 postgresql을 기반으로한 opensql 기술 문서입니다. 

 

개요

본 문서에서는 PostgreSQL을 위한 절차적 언어로 데이터베이스 서버 설치 시 기본적으로 설치되어 있는 PL/pgSQL에 대해 안내합니다. 

postgres=# dx
List of installed extensions
Name | Version | Schema | Description
——————–+———+————+————————————————————————
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
  • Function, Procedure, Trigger 생성에 사용 가능하며, SQL 에 제어 구조가 포함된 형태입니다.
  • 모든 User-Defined Type, Function, Procedure, Operator 를 상속합니다.
  • PL/pgSQL 은 서버 내에서 동작하므로 신뢰성을 가지고 있습니다.

 

PL/pgSQL 사용 시 장점

  • 데이터베이스 내부에서 동작하므로 프로세스 통신 오버헤드가 적습니다.
  • SQL의 모든 데이터 유형, 연산자 및 기능을 사용할 수 있습니다.

참고

아래는 PL/pgSQL 의 내용을 설명하고, 예시를 토글로 만들었습니다.
직접 쿼리를 입력해보면서 확인할 것을 권장합니다. 

 

방법

PL/pgSQL Block Structure

[<>]
DECLARE
변수를 선언합니다.
변수의 생명주기는 블록 안으로 제한됩니다. (지역변수)
연관된(Nested) 블록 안에서 변수가 덮어 씌워질 수 있으며,
바깥 블록의 라벨을 명시해서 호출할 수 있습니다.
모든 SQL 데이터 타입이 지원되며, %TYPE이라는 키워드를 사용하여,
테이블의 컬럼을 참조할 수 있습니다.
BEGIN
제어구조
서비스 연산자를 제외한 SQL 연산자
EXCEPTION ;
에러 핸들링 부분을 구현합니다.
END [label];

 

PL/pgSQL Block Structure 예시

CREATE FUNCTION example() RETURNS integer AS $$
<< outerblock >>
DECLARE
quantity integer := 30;
BEGIN
RAISE NOTICE ‘Quantity here is %’, quantity; — Prints 30
quantity := 50;
— Create a sub block
<>
DECLARE
quantity integer := 80;
BEGIN
RAISE NOTICE ‘Quantity here is %’, quantity; — Prints 80
RAISE NOTICE ‘Outer quantity here is %’, outerblock.quantity; — Prints 50
END subblock;
RAISE NOTICE ‘Quantity here is %’, quantity; — Prints 50
RETURN quantity;
END outerblock;

$$ LANGUAGE plpgsql;

— 수행 결과
postgres=# SELECT example();
NOTICE: Quantity here is 30
NOTICE: Quantity here is 80
NOTICE: Outer quantity here is 50
NOTICE: Quantity here is 50

example

  50
(1 row)

 

Anonymous Block Structure

데이터베이스 서버에 PL/pgSQL 코드를 저장하지 않고, DO 커맨드를 이용하여 일회성으로 실행할 수 있습니다. 파라미터를 사용하거나 리턴 값을 받을 수 없습니다.

— 구조
DO $$
<>DECLARE
변수 선언
BEGIN
제어구조
EXCEPTION ; 에러 핸들링
END [block label];

 

Anonymous Block Structure 예시

DO $$
<>
DECLARE
v1_int integer;
v2_txt text;
BEGIN
SELECT 1 INTO v1_int;
SELECT ‘Anonymous block test’ INTO v2_txt;
RAISE NOTICE ‘%, %’, v1_int, v2_txt;
END outblock;
$$ LANGUAGE plpgsql;

— 수행 결과
postgres=# DO $$
postgres$# <>
postgres$# DECLARE
postgres$# v1_int integer;
postgres$# v2_txt text;
postgres$# BEGIN
postgres$# SELECT 1 INTO v1_int;
postgres$# SELECT ‘Anonymous block test’ INTO v2_txt;
postgres$# RAISE NOTICE ‘%, %’, v1_int, v2_txt;
postgres$# END outblock;
postgres$# $$ LANGUAGE plpgsql;
NOTICE: 1, Anonymous block test
DO

 

변수 선언

loop 변수, 커서 결과를 위한 변수를 제외하고, 블록 안에서 사용되는 모든 변수들은 DECLARE 섹션에서 선언되어야 합니다.

 

General Syntax

name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];

변수의 초기 값은 DEFAULT, :=, = 세가지 표현을 이용해서 expression 값을 변수에 할당할 수 있습니다. 초기 값을 지정하지 않으면 NULL 값을 가지게 됩니다.
Function 또는 Procedure 의 파라미터의 값으로 변수를 초기화할 수도 있습니다.
파라미터를 쉽게 사용하기 위해 바인드 변수와 같은 예약어인 $1, $2, … $n 사용이 가능합니다.
알아보기 쉽게 사용할 수 있도록 ALIAS 기능도 지원합니다.

name ALIAS FOR $n;

 

변수 선언 예시

DO $$
DECLARE
v1_int integer;
v2_int integer DEFAULT 2;
v3_int integer := 3;
v4_int integer = 4;
BEGIN
RAISE NOTICE ‘%, %, %, %’, v1_int, v2_int, v3_int, v4_int;
END
$$ LANGUAGE plpgsql;

— 수행 결과
postgres=# DO $$
postgres$# DECLARE
postgres$# v1_int integer;
postgres$# v2_int integer DEFAULT 2;
postgres$# v3_int integer := 3;
postgres$# v4_int integer = 4;
postgres$# BEGIN
postgres$# RAISE NOTICE ‘%, %, %, %’, v1_int, v2_int, v3_int, v4_int;
postgres$# END
postgres$# $$ LANGUAGE plpgsql;
NOTICE: , 2, 3, 4
DO

 

상수 선언

변수 선언 시 TYPE 앞에 CONSTANT 를 붙임으로서 상수로 지정할 수 있습니다.
상수는 초기화 시점을 제외하고 데이터 값의 변경이 허용되지 않으며, 변경 시도 시 오류가 발생합니다.
초기화 시에 값을 지정해주지 않으면, NULL 값으로 생성됩니다.

name [CONSTANT] type;

 

상수 선언 예시

DO $$
DECLARE
v1_const_int constant integer;
BEGIN
SELECT 1 INTO v1_const_int;
RAISE NOTICE ‘%’, v1_const_int;
END
$$ LANGUAGE plpgsql;

— 수행 결과
postgres=# DO $$
postgres$# DECLARE
postgres$# v1_const_int constant integer;
postgres$# BEGIN
postgres$# SELECT 1 INTO v1_const_int;
postgres$# RAISE NOTICE ‘%’, v1_const_int;
postgres$# END
postgres$# $$ LANGUAGE plpgsql;
ERROR: variable “v1_const_int” is declared CONSTANT
LINE 5: SELECT 1 INTO v1_const_int;
^

 

Collation

데이터 정렬에 사용할 Collation 타입을 지정할 수 있습니다.
지정하지 않을 경우, 데이터베이스의 Collation 타입으로 설정됩니다.

PostgreSQL 에서는 ko_KR 타입을 이용하여 문자열 정렬이 가능 하지만, 글자 수에 따라서 정렬 결과가 달라지므로 완벽한 한글 정렬을 위해선 Collation 을 C 로 지정해줘야 합니다.

 

Collation 예시

CREATE TABLE coll_t1(name text);
INSERT INTO coll_t1 VALUES(‘가’),(‘나’),(‘가나다’),(‘가다나’),(‘나가’);
SELECT * FROM coll_t1 ORDER BY name ASC;
SELECT * FROM coll_t1 ORDER BY name COLLATE “C” ASC;

— 수행 결과
postgres=# SELECT * FROM coll_t1 ORDER BY name ASC;

name

가
가
나
나
나가
나가
가나다
가나다
가다나
가다나
(10 rows)

postgres=# SELECT * FROM coll_t1 ORDER BY name COLLATE “C” ASC;

name

가
가
가나다
가나다
가다나
가다나
나
나
나가
나가
(10 rows)

 

Copying type

%TYPE 타입을 사용하면, 다른 변수의 데이터 타입이나 테이블의 특정 컬럼의 데이터 타입을 가진 변수를 생성할 수 있습니다.

참조 대상의 데이터 타입이 변경되어도 신경쓰지 않아도 되므로, 다형성 함수에서 사용하기에 용이합니다. 
단, 사용자 정의 타입은 복사할 수 없습니다.
 

General Syntax

변수 참조할변수%TYPE

 

Table Column 참조 예시

VARIABLE TABLENAME.COLUMN%TYPE

 

Copying type 예시

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id integer, password text);
INSERT INTO t1 VALUES(1, ‘pass1’);

DO $$
DECLARE
v1_int integer;
v2_copy_v1 v1_int%TYPE;
v3_copy_t1_id t1.id%TYPE;
BEGIN
SELECT id INTO v1_int FROM t1 LIMIT 1;
SELECT id INTO v2_copy_v1 FROM t1 LIMIT 1;
SELECT 3 INTO v3_copy_t1_id;
RAISE NOTICE ‘%, %, %’, v1_int, v2_copy_v1, v3_copy_t1_id;
END
$$ LANGUAGE plpgsql;

postgres=# DROP TABLE IF EXISTS t1;
DROP TABLE
postgres=# CREATE TABLE t1 (id integer, password text);
CREATE TABLE
postgres=# INSERT INTO t1 VALUES(1, ‘pass1’);
INSERT 0 1
postgres=#
postgres=# DO $$
postgres$# DECLARE
postgres$# v1_int integer;
postgres$# v2_copy_v1 v1_int%TYPE;
postgres$# v3_copy_t1_id t1.id%TYPE;
postgres$# BEGIN
postgres$# SELECT id INTO v1_int FROM t1 LIMIT 1;
postgres$# SELECT id INTO v2_copy_v1 FROM t1 LIMIT 1;
postgres$# SELECT 3 INTO v3_copy_t1_id;
postgres$# RAISE NOTICE ‘%, %, %’, v1_int, v2_copy_v1, v3_copy_t1_id;
postgres$# END
postgres$# $$ LANGUAGE plpgsql;
NOTICE: 1, 1, 3
DO

 

Row type

Row 변수 또는 Row 타입 변수라고도 부르며, SELECT 또는 FOR 쿼리의 결과를 row 형태로 가지고 있을 수 있습니다.
변수가 가지고 있는 컬럼에 할당된 값은 row변수명.컬럼명 형태로 접근할 수 있습니다.

테이블 또는 뷰의 이름을 데이터 타입으로 지정해서 사용 가능 합니다.
%ROWTYPE을 명시하지 않아도 PostgreSQL에서는 테이블명을 변수명으로 사용할 수 없기 때문에 문제가 되지 않지만, 타 DBMS 호환성을 높이고 싶다면 사용하는 것이 좋습니다.

 

General Syntax

name table_name%ROWTYPE;
name composite_type_name;

 

Row type 예시

DROP IF EXISTS TABLE t1;
CREATE TABLE t1 (id integer, password text);
INSERT INTO t1 VALUES(1, ‘pass1’);

DO $$
DECLARE
v1_row t1%ROWTYPE;
BEGIN
RAISE NOTICE ‘%, %’, v1_row.id, v1_row.password;
SELECT * INTO v1_row FROM t1;
RAISE NOTICE ‘%, %’, v1_row.id, v1_row.password;
END
$$ LANGUAGE plpgsql;

— 수행 결과
postgres=# DROP TABLE IF EXISTS t1;
DROP TABLE
postgres=# CREATE TABLE t1 (id integer, password text);
CREATE TABLE
postgres=# INSERT INTO t1 VALUES(1, ‘pass1’);
INSERT 0 1
postgres=#
postgres=# DO $$
postgres$# DECLARE
postgres$# v1_row t1%ROWTYPE;
postgres$# BEGIN
postgres$# RAISE NOTICE ‘%, %’, v1_row.id, v1_row.password;
postgres$# SELECT * INTO v1_row FROM t1;
postgres$# RAISE NOTICE ‘%, %’, v1_row.id, v1_row.password;
postgres$# END
postgres$# $$ LANGUAGE plpgsql;
NOTICE: ,
NOTICE: 1, pass1
DO

 

Record type

Record 타입은 row 타입과 유사하지만, row 정보를 입력 받기 전에는 컬럼 정보에 대한 구조를 가지고 있지 않습니다.
SELECT 또는 FOR 쿼리 등으로 데이터를 입력 받아야 row에 필요한 컬럼 구조가 할당 되는 변수입니다.

다른 구조의 쿼리 값을 할당 받으면 record 변수가 가지고 있는 컬럼 정보가 변경됩니다.
구조를 할당 받기 이전에 해당 변수에 접근하면 런타임 에러가 발생하게 됩니다.

 

General Syntax

name RECORD;

 

Record Type 예시

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id integer, password text);
INSERT INTO t1 VALUES(1, ‘pass1’);

DO $$
DECLARE
v1_rec RECORD;
BEGIN
— RAISE NOTICE ‘%, %’, v1_rec.id, v1_rec.password;
SELECT * INTO v1_rec FROM t1;
RAISE NOTICE ‘%, %’, v1_rec.id, v1_rec.password;
END
$$ LANGUAGE plpgsql;

— 수행 결과
postgres=# DROP TABLE IF EXISTS t1;
DROP TABLE
postgres=# CREATE TABLE t1 (id integer, password text);
CREATE TABLE
postgres=# INSERT INTO t1 VALUES(1, ‘pass1’);
INSERT 0 1
postgres=#
postgres=# DO $$
postgres$# DECLARE
postgres$# v1_rec RECORD;
postgres$# BEGIN
postgres$# — RAISE NOTICE ‘%, %’, v1_rec.id, v1_rec.password;
postgres$# SELECT * INTO v1_rec FROM t1;
postgres$# RAISE NOTICE ‘%, %’, v1_rec.id, v1_rec.password;
postgres$# END
postgres$# $$ LANGUAGE plpgsql;
NOTICE: 1, pass1
DO

 

Conditional Operators and loops

조건 제어문

PL/pgSQL 에서는 두 가지 조건 제어문을 제공합니다.

  • IF-THEN 제어문
  • CASE-WHEN-THEN 제어문

 

IF-THEN 제어문

조건을 검사하고 충족되면 수행되는 구조입니다.
IF-THEN 제어문에서 아래와 같이 3가지 키워드를 제공합니다.
모든 IF-THEN 제어문은 END IF; 로 종료되어야 합니다.

IF boolean-expression THEN
…
[ELSIF boolean-expression THEN
…]
[ELSE
…]
END IF;

 

IF-THEN 제어문 예시

DO $$
DECLARE
result TEXT;
number INTEGER := 1;
BEGIN
IF number = 0 THEN
result := ‘zero’;
ELSIF number > 0 THEN
result := ‘positive’;
ELSIF number < 0 THEN
result := ‘negative’;
ELSE
result := ‘NULL’;
END IF;
RAISE NOTICE ‘%’,result;
END;
$$ LANGUAGE plpgsql;

— 수행 결과
postgres=# DO $$
postgres$# DECLARE
postgres$# result TEXT;
postgres$# number INTEGER := 1;
postgres$# BEGIN
postgres$# IF number = 0 THEN
postgres$# result := ‘zero’;
postgres$# ELSIF number > 0 THEN
postgres$# result := ‘positive’;
postgres$# ELSIF number < 0 THEN
postgres$# result := ‘negative’;
postgres$# ELSE
postgres$# result := ‘NULL’;
postgres$# END IF;
postgres$# RAISE NOTICE ‘%’,result;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
NOTICE: positive
DO

 

CASE-WHEN-THEN

CASE-WHEN-THEN 제어문은 IF-THEN 제어문과 마찬가지로, 조건을 검사하고 충족할 경우 수행됩니다.
모든 CASE-WHEN-THEN 제어문은 END CASE; 로 종료되어야 합니다.

CASE 문은 아래와 같은 두가지 형태를 가질 수 있습니다.

  • 피연산자의 값을 비교하는 방식
  • WHEN 절 마다 특정 조건을 비교하는 방식

 

피연산자의 값을 비교하는 방식

CASE search-expression
WHEN expression [, expression [ … ]] THEN
statements
[ WHEN expression [, expression [ … ]] THEN
statements
… ]
[ ELSE
statements ]
END CASE;

 

피연산자의 값을 비교하는 방식의 CASE-WHEN 예시

DO $$
DECLARE
x integer := 1;
msg TEXT;
BEGIN
CASE x
WHEN 1, 2 THEN
msg := ‘one or two’;
ELSE
msg := ‘other value than one or two’;
END CASE;
RAISE NOTICE ‘%’,msg;
END;
$$ LANGUAGE plpgsql;

— 수행 결과
postgres=# DO $$
postgres$# DECLARE
postgres$# x integer := 1;
postgres$# msg TEXT;
postgres$# BEGIN
postgres$# CASE x
postgres$# WHEN 1, 2 THEN
postgres$# msg := ‘one or two’;
postgres$# ELSE
postgres$# msg := ‘other value than one or two’;
postgres$# END CASE;
postgres$# RAISE NOTICE ‘%’,msg;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
NOTICE: one or two
DO

 

WHEN 절 마다 특정 조건을 비교하는 방식

CASE
WHEN boolean-expression THEN
statements
[ WHEN boolean-expression THEN
statements
… ]
[ ELSE
statements ]
END CASE;

 

WHEN 절 마다 특정 조건을 비교하는 방식 예시

DO $$
DECLARE
x integer := 1;
msg TEXT;
BEGIN
CASE
WHEN x BETWEEN 0 AND 10 THEN
msg := ‘value is between zero and ten’;
WHEN x BETWEEN 11 AND 20 THEN
msg := ‘value is between eleven and twenty’;
END CASE;
RAISE NOTICE ‘%’,msg;
END;
$$ LANGUAGE plpgsql;

— 수행 결과
postgres=# DO $$
postgres$# DECLARE
postgres$# x integer := 1;
postgres$# msg TEXT;
postgres$# BEGIN
postgres$# CASE
postgres$# WHEN x BETWEEN 0 AND 10 THEN
postgres$# msg := ‘value is between zero and ten’;
postgres$# WHEN x BETWEEN 11 AND 20 THEN
postgres$# msg := ‘value is between eleven and twenty’;
postgres$# END CASE;
postgres$# RAISE NOTICE ‘%’,msg;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
NOTICE: value is between zero and ten
DO

 

반복문

PL/pgSQL 에서는 아래와 같은 반복문들을 제공합니다.

  • LOOP-EXIT
  • WHILE 반복문
  • FOR IN 반복문

  • FOREACH IN ARRAY 반복문

     

LOOP-EXIT 반복문

무한 반복되는 반복문이며, EXIT 나 RETURN 을 입력 받으면 수행이 종료됩니다.
EXIT 는 WHEN 을 사용하여 조건을 줄 수 있습니다.
CONTINUE를 사용하여 조건 만족 시 뒤의 문장을 실행하지 않고 다음 회차의 반복으로 넘어가게 됩니다.

[ <> ]
LOOP
[CONTINUE [LABEL] [WHEN condition]];
statements
[EXIT [LABEL] [WHEN condition]];
END LOOP [ label ];

 

LOOP-EXIT 반복문 예시

DO $$
DECLARE
x integer := 0;
BEGIN
LOOP
x = x + 1;
CONTINUE WHEN x = 5;
RAISE NOTICE ‘x : %’,x;
EXIT WHEN x > 10;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— 수행 결과
postgres=# DO $$
postgres$# DECLARE
postgres$# x integer := 0;
postgres$# BEGIN
postgres$# LOOP
postgres$# x = x + 1;
postgres$# CONTINUE WHEN x = 5;
postgres$# RAISE NOTICE ‘x : %’,x;
postgres$# EXIT WHEN x > 10;
postgres$# END LOOP;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
NOTICE: x : 1
NOTICE: x : 2
NOTICE: x : 3
NOTICE: x : 4
NOTICE: x : 6
NOTICE: x : 7
NOTICE: x : 8
NOTICE: x : 9
NOTICE: x : 10
NOTICE: x : 11
DO

 

WHILE 반복문

WHILE에 명시한 조건이 충족될 때 까지 무한으로 반복되는 반복문 입니다.

명시한 조건이 충족되지 않으면 무한으로 반복되게 하고싶을 경우 WHILE NOT을 사용할 수 있습니다. 다른 반복문과 마찬가지로 CONTINUE를 사용하여 특정 조건을 건너뛰거나 EXIT를 사용할 수 있습니다.

[ <> ]
WHILE boolean-expression LOOP
[CONTINUE [LABEL] [WHEN condition]];
statements
END LOOP [ label ];

[ <> ]
WHILE NOT boolean-expression LOOP
[CONTINUE [LABEL] [WHEN condition]];
statements
END LOOP [ label ];

 

WHILE 반복문 예시

DO $$
DECLARE
x integer := 0;
BEGIN
WHILE x < 11 LOOP
x = x + 1;
CONTINUE WHEN x = 5;
RAISE NOTICE ‘x : %’,x;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— 수행 결과
postgres=# DO $$
postgres$# DECLARE
postgres$# x integer := 0;
postgres$# BEGIN
postgres$# WHILE x < 11 LOOP
postgres$# x = x + 1;
postgres$# CONTINUE WHEN x = 5;
postgres$# RAISE NOTICE ‘x : %’,x;
postgres$# END LOOP;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
NOTICE: x : 1
NOTICE: x : 2
NOTICE: x : 3
NOTICE: x : 4
NOTICE: x : 6
NOTICE: x : 7
NOTICE: x : 8
NOTICE: x : 9
NOTICE: x : 10
NOTICE: x : 11
DO

 

WHILE NOT 반복문 예시

DO $$
DECLARE
x integer := 0;
BEGIN
WHILE NOT x > 10 LOOP
x = x + 1;
CONTINUE WHEN x = 5;
RAISE NOTICE ‘x : %’,x;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— 수행 결과
postgres=# DO $$
postgres$# DECLARE
postgres$# x integer := 0;
postgres$# BEGIN
postgres$# WHILE NOT x > 10 LOOP
postgres$# x = x + 1;
postgres$# CONTINUE WHEN x = 5;
postgres$# RAISE NOTICE ‘x : %’,x;
postgres$# END LOOP;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
NOTICE: x : 1
NOTICE: x : 2
NOTICE: x : 3
NOTICE: x : 4
NOTICE: x : 6
NOTICE: x : 7
NOTICE: x : 8
NOTICE: x : 9
NOTICE: x : 10
NOTICE: x : 11
DO

 

FOR-IN 반복문

IN range에 명시된 범위만큼 루프변수가 증감되며 반복되는 반복문 입니다.
REVERSE를 명시하면 루프변수의 값이 증가되지 않고 감소되며 반복합니다.

BY절을 이용하여, 증감 단위를 설정할 수 있습니다.
다른 반복문과 마찬가지로 CONTINUE와 WHEN을 사용할 수 있습니다.

[ <> ]
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP
statements
END LOOP [ label ];

 

쿼리 결과의 모든 ROW 를 대상으로 반복문을 실행할 수도 있습니다.

[ <> ]
FOR target IN query LOOP
statements
END LOOP [ label ];

 

FOR-IN 반복문 예시

DO $$
BEGIN
FOR x IN 1..10 LOOP
CONTINUE WHEN x=5;
RAISE NOTICE ‘x : %’,x;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— 수행 결과
postgres=# DO $$
postgres$# BEGIN
postgres$# FOR x IN 1..10 LOOP
postgres$# CONTINUE WHEN x=5;
postgres$# RAISE NOTICE ‘x : %’,x;
postgres$# END LOOP;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
NOTICE: x : 1
NOTICE: x : 2
NOTICE: x : 3
NOTICE: x : 4
NOTICE: x : 6
NOTICE: x : 7
NOTICE: x : 8
NOTICE: x : 9
NOTICE: x : 10
DO

 

FOR-IN 쿼리 내용 반복문 예시

DO $$
DECLARE
row RECORD;
BEGIN
FOR row IN SELECT * FROM pg_database
LOOP
CONTINUE WHEN row.oid=1;
RAISE NOTICE ‘oid : % | datname : %’, row.oid, row.datname;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— 수행 결과
postgres=# DO $$
postgres$# DECLARE
postgres$# row RECORD;
postgres$# BEGIN
postgres$# FOR row IN SELECT * FROM pg_database
postgres$# LOOP
postgres$# CONTINUE WHEN row.oid=1;
postgres$# RAISE NOTICE ‘oid : % | datname : %’, row.oid, row.datname;
postgres$# END LOOP;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
NOTICE: oid : 14486 | datname : postgres
NOTICE: oid : 14485 | datname : template0
DO

 

FOREACH IN ARRAY 반복문

IN ARRAY에 명시한 배열의 요소를 탐색하는 반복문입니다.
다른 반복문과 마찬가지로 CONTINUE와 WHEN을 사용할 수 있습니다.

SLICE를 이용하여 다차원 배열의 원소를 반복할 수 있습니다.
다차원 배열의 원소를 반복하기 위해서는 루프변수가 ARRAY 타입이어야 합니다.

[ <> ]
FOREACH target [ SLICE number ] IN ARRAY expression LOOP
statements
END LOOP [ label ];

 

FOREACH IN ARRAY 반복문 예시

DO $$
DECLARE
x int[] := ARRAY[1,2,3,5,6];
i int;
BEGIN
FOREACH i IN ARRAY x
LOOP
RAISE NOTICE ‘row : %’,i;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— 수행 결과
postgres=# DO $$
postgres$# DECLARE
postgres$# x int[] := ARRAY[1,2,3,5,6];
postgres$# i int;
postgres$# BEGIN
postgres$# FOREACH i IN ARRAY x
postgres$# LOOP
postgres$# RAISE NOTICE ‘row : %’,i;
postgres$# END LOOP;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
NOTICE: row : 1
NOTICE: row : 2
NOTICE: row : 3
NOTICE: row : 5
NOTICE: row : 6
DO

 

FOREACH IN ARRAY 반복문 예시 – 다차원 배열

DO $$
DECLARE
x int[] := ARRAY[[1,2],[3,4],[5,6],[7,8]];
i int[];
BEGIN
FOREACH i SLICE 1 IN ARRAY x
LOOP
RAISE NOTICE ‘row : %’,i;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— 수행 결과
postgres=# DO $$
postgres$# DECLARE
postgres$# x int[] := ARRAY[[1,2],[3,4],[5,6],[7,8]];
postgres$# i int[];
postgres$# BEGIN
postgres$# FOREACH i SLICE 1 IN ARRAY x
postgres$# LOOP
postgres$# RAISE NOTICE ‘row : %’,i;
postgres$# END LOOP;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
NOTICE: row : {1,2}
NOTICE: row : {3,4}
NOTICE: row : {5,6}
NOTICE: row : {7,8}
DO

 

 

본 문서는 postgresql을 기반으로한 opensql 기술 문서입니다. 

 

관련 대상