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;

마무리하며

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

개발자스럽다