PostgreSQL: INSERT, UPDATE, DELETE 실행 결과 리턴 받기 (WHEN / RETURNING)


PGSQL에서는 INSERT, UPDATE, DELETE 쿼리 실행 후 처리 ROWS만 알려주는데, 조금더 상세한 정보를 알수 있는 방법이 있습니다.
INSERT, UPDATE, DELETE 쿼리 뒤에 RETURNING *난 입력해주면 실행한 쿼리의 결과를 출력해주게됩니다.
UPDATE member SET id = 't' RETURNING *;

RETURNING * 에서 *SELECT * FROM member;와 같습니다. * 대신 컬럼 명을 입력해도 됩니다.
이 기능을 언제 쓰면 좋을지 활용 예제를 참고해보세요.
시작전에 PGSQL에서 WITH 8.4이상, RETURNING 8.2 이상 버전에서 사용 가능합니다.

활용 예제를 테스트 하기 위한 테이블 생성

-- 회원
CREATE TABLE member (
    id VARCHAR(10) PRIMARY KEY,
    password VARCHAR(10)
);
-- 회원 상세
CREATE TABLE member_detail (
    id VARCHAR(10) PRIMARY KEY,
    name VARCHAR(10)
);
-- 회원 로그
CREATE TABLE member_log (
    id VARCHAR(10),
    description TEXT,
    created TIMESTAMP
);
-- 회원 백업
CREATE TABLE member_backup AS SELECT * FROM member;
-- 회원 테스트 데이터
INSERT INTO member VALUES
    ('backup-id0', '12345678'),
    ('backup-id1', '12345678'),
    ('backup-id2', '12345678');


INSERT 활용 예제 1

설명: 회원 기본 정보를 member 테이블에 정상 입력되면 id 값을 받아 member_detail 테이블에 상세 정보를 넣는다.
WITH rows AS (
    INSERT INTO member (id, password)
    VALUES ('taekyun', 'pass1234') RETURNING id
)
INSERT INTO member_detail (id, name)
    SELECT id, '김태균' AS name
    FROM rows;


INSERT 활용 예제 2

설명: WITH에 다중 쿼리를 사용 예제로, member, member_detail 테이블에 동시에 입력 후 두 테이블의 정보를 member_log 테이블에 로그를 넣는다.
WITH new_member AS (
    INSERT INTO member (id, password)
    VALUES ('taekyun2', 'pass1234') RETURNING *
), new_member_detail AS (
    INSERT INTO member_detail (id, name)
    VALUES ('taekyun2', '김태균2') RETURNING *
)
INSERT INTO member_log (id, description, created)
    SELECT new_member.id, new_member_detail.name||' 추가', NOW()
    FROM new_member, new_member_detail;


UPDATE 활용 예제

설명: member테이블의 password 값을 업데이트하고 업데이트가 된 회원 아이디를 member_log 테이블에 넣는다.
WITH updated AS (
    UPDATE member SET password = 'pAss@123' WHERE password = 'pass1234' RETURNING id
)
INSERT INTO member_log (id, description, created)
    SELECT updated.id, updated.id||' 수정', NOW()
    FROM updated;


DELETE 활용 예제

설명: member테이블에서 password 값이 12345678인 값은 삭제하고 삭제된 회원을 member_backup 테이블에 넣는다.
WITH deleted AS (
    DELETE FROM member WHERE password = '12345678' RETURNING *
)
INSERT INTO member_backup SELECT * FROM deleted;


마무리하며

활용 예제는 너무 단순하지만 잘 활용하면 유용한 쿼리입니다.

함께보기




database
10월 5, 2015
0

Menu

Search

Popular Posts

디자이너를 위한 최고의 영화 (디자이너에게 영감을 주는 영화)

개인적으로 직접 조립하고 만드는걸 좋아했는데 만들기전에 만들어질 모습을 스케치하는 것을 즐겼습니다. 스케치 할때 머리 속에 있는 멋진 모습…

Bash 입문자를 위한 핵심 요약 정리 (Shell Script)

Bash 입문자를 위한 기본적인 문법과 알고 있으면 좋은 것들만 정리했다. (이런 것도 있구나 싶을 정도만 이해하도록 간단한 설명과 예제로 …

PHP7 공개 전 참고 사이트 소개

PHP7 공개가 2015년 10월이라고 한다. 그래서 인지 모르겠지만 부쩍 PHP7에 대한 글들을 많이 찾아볼 수 있어 참고해볼 만한 …

Contact Me