RETURNING Clause

Setelah INSERT, UPDATE, atau DELETE dijalankan, aplikasi hampir selalu butuh tahu hasil persisnya: id apa yang barusan dihasilkan database? Harga baru setelah kenaikan berapa? Baris mana saja yang terhapus? Kebutuhan itu lazim dijawab dengan query SELECT kedua — rawan race condition, dan untuk DELETE datanya sudah tidak ada sehingga tidak bisa dipilih lagi. Klausa RETURNING, fitur khas PostgreSQL sejak versi 8.2, membalikkan data itu di dalam perintah yang sama: baris yang baru disisipkan, diubah, atau dihapus dikembalikan seakan-akan perintah DML itu adalah sebuah SELECT.

Menyiapkan Tabel Latihan #

Fitur ini paling bersinar ketika id dihasilkan oleh database, jadi tabel produk kita memakai identity column — kolom yang nilainya diisi PostgreSQL otomatis dan tidak kita tulis saat INSERT:

CREATE TABLE produk (
    id       integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama     text NOT NULL,
    kategori text NOT NULL,
    harga    numeric(10, 2) NOT NULL CHECK (harga > 0),
    stok     integer NOT NULL DEFAULT 0
);

INSERT INTO produk (nama, kategori, harga, stok) VALUES
    ('Buku Tulis', 'alat tulis', 4500, 120),
    ('Pulpen Hitam', 'alat tulis', 3000, 200),
    ('Kertas HVS A4', 'kertas', 55000, 40),
    ('Amplop Coklat', 'kertas', 1500, 0);

SELECT id, nama, kategori, harga, stok FROM produk ORDER BY id;

Output yang Dihasilkan:

CREATE TABLE
INSERT 0 4
 id |     nama      |  kategori  |  harga   | stok 
----+---------------+------------+----------+------
  1 | Buku Tulis    | alat tulis |  4500.00 |  120
  2 | Pulpen Hitam  | alat tulis |  3000.00 |  200
  3 | Kertas HVS A4 | kertas     | 55000.00 |   40
  4 | Amplop Coklat | kertas     |  1500.00 |    0
(4 rows)

Keempat baris mendapat id 1 sampai 4 tanpa kita menyebutkan angka itu sama sekali. Persoalannya: kalau aplikasi perlu menyimpan id produk baru ke tabel lain (misalnya detail pesanan), angka mana yang harus dipakai? RETURNING menjawabnya langsung di tempat.

RETURNING pada INSERT #

Cukup tambahkan RETURNING di belakang INSERT diikuti daftar kolom — bentuknya sama seperti daftar select pada SELECT:

INSERT INTO produk (nama, kategori, harga, stok)
VALUES ('Map Plastik', 'alat tulis', 12000, 25)
RETURNING id, nama, harga;

INSERT INTO produk (nama, kategori, harga, stok)
VALUES ('Kertas Folio', 'kertas', 20000, 50)
RETURNING *;

Output yang Dihasilkan:

 id |    nama     |  harga   
----+-------------+----------
  5 | Map Plastik | 12000.00
(1 row)

INSERT 0 1
 id |     nama     | kategori |  harga   | stok 
----+--------------+----------+----------+------
  6 | Kertas Folio | kertas   | 20000.00 |   50
(1 row)

INSERT 0 1

Dua variasi sekaligus terlihat di output. RETURNING id, nama, harga mengembalikan tiga kolom — termasuk id = 5 yang dihitung server pada saat itu juga, angka yang tidak mungkin kita ketahui sebelumnya. RETURNING * mengembalikan seluruh kolom baris baru, id = 6 untuk Kertas Folio. Perhatikan urutan output psql: baris hasil RETURNING tampil lebih dulu, lalu diikuti tag INSERT 0 1 — tag tetap tercetak seperti biasa di baris terakhir.

Inilah mengapa RETURNING menyelesaikan masalah klasik “ambil id terbaru”: tanpa RETURNING, aplikasi terpaksa menjalankan SELECT id FROM produk ORDER BY id DESC LIMIT 1 — query terpisah yang bisa mengembalikan id baris milik pengguna lain kalau ada INSERT lain menyusul di antara keduanya. Dengan RETURNING, id yang benar untuk baris kita sendiri datang sebagai bagian dari jawaban perintah yang sama.

RETURNING pada UPDATE #

Pada UPDATE, RETURNING mengembalikan keadaan baru setiap baris yang berubah — nilai setelah SET diterapkan, bukan nilai lama:

UPDATE produk
SET harga = harga * 1.1
WHERE kategori = 'alat tulis'
RETURNING id, nama, harga AS harga_baru;

UPDATE produk
SET stok = stok + 20
WHERE id = 3
RETURNING id, nama, harga, stok;

Output yang Dihasilkan:

 id |     nama     | harga_baru 
----+--------------+------------
  1 | Buku Tulis   |    4950.00
  2 | Pulpen Hitam |    3300.00
  5 | Map Plastik  |   13200.00
(3 rows)

UPDATE 3
 id |     nama      |  harga   | stok 
----+---------------+----------+------
  3 | Kertas HVS A4 | 55000.00 |   60
(1 row)

UPDATE 1

Perintah pertama menaikkan harga kategori alat tulis sepuluh persen dan mengembalikan harga baru untuk ketiga produk itu: Buku Tulis jadi 4950.00, Pulpen Hitam jadi 3300.00, Map Plastik jadi 13200.00. Alias AS harga_baru di daftar RETURNING bekerja persis seperti alias pada SELECT. Perintah kedua memperlihatkan pemeriksaan stok baru Kertas HVS A4 (60) tanpa SELECT susulan. Nilai lama baris tidak dikirim balik — PostgreSQL versi 17 memang hanya mengembalikan keadaan akhir; pola yang butuh nilai lama dan baru sekaligus biasanya memakai CTE bertingkat, topik Section Subquery dan CTE.

RETURNING pada DELETE #

DELETE adalah pemanfaatan paling unik: begitu baris terhapus, datanya tidak bisa di-SELECT lagi — kecuali kita memintanya pada saat penghapusan. RETURNING menangkap potongan data itu sebelum lenyap:

DELETE FROM produk
WHERE stok = 0
RETURNING id, nama;

Output yang Dihasilkan:

 id |     nama      
----+---------------
  4 | Amplop Coklat
(1 row)

DELETE 1

Baris Amplop Coklat dengan id = 4 dan stok = 0 keluar dari tabel, dan pada saat yang sama identitasnya tercatat di output. Pola ini pas untuk proses seperti membersihkan data kedaluwarsa ke tabel arsip: baris yang dihapus tercatat di aplikasi atau di log tanpa perlu membaca tabel sebelum menghapus. Perhatikan juga bahwa tanpa RETURNING, kita hanya menerima tag DELETE 1 — satu angka tanpa nama.

Batasan RETURNING: Aggregate Tidak Diizinkan #

Daftar RETURNING memang semirip SELECT, tetapi ada satu larangan tegas: aggregate function seperti count(*) tidak boleh muncul di dalamnya. Mencoba menghitung sendiri jumlah baris yang dihapus akan ditolak:

DELETE FROM produk
WHERE id = 6
RETURNING count(*) AS jumlah_terhapus;

Output yang Dihasilkan:

psql:returning-agregat-error.sql:3: ERROR:  aggregate functions are not allowed in RETURNING
LINE 3: RETURNING count(*) AS jumlah_terhapus;
                  ^

Alasannya mekanis: RETURNING menghasilkan data per baris yang tersentuh perintah, sedangkan aggregate butuh seluruh baris sekaligus — dua model eksekusi yang tidak bisa dicampur dalam satu tahap. Untuk menghitung jumlah baris, tag perintah (DELETE 3, UPDATE 2) sudah memuat angkanya; aplikasi tinggal membaca property row count dari driver. Ekspresi biasa — kolom, alias, harga * 1.1 — tetap sah di daftar RETURNING, sehingga batas praktisnya memang hanya agregasi.

Klausa ini akan terus muncul di sisa section, karena RETURNING melengkapi hampir setiap bentuk DML: INSERT multi-baris, ON CONFLICT, sampai menghitung hasil batch. Kita mulai dari yang paling berdampak sehari-hari: memasukkan banyak baris sekaligus — topik chapter berikutnya.

Referensi #

• INSERT Reference
• UPDATE Reference
• DELETE Reference

CREATE TABLE produk (
    id       integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama     text NOT NULL,
    kategori text NOT NULL,
    harga    numeric(10, 2) NOT NULL CHECK (harga > 0),
    stok     integer NOT NULL DEFAULT 0
);

INSERT INTO produk (nama, kategori, harga, stok) VALUES
    ('Buku Tulis', 'alat tulis', 4500, 120),
    ('Pulpen Hitam', 'alat tulis', 3000, 200),
    ('Kertas HVS A4', 'kertas', 55000, 40),
    ('Amplop Coklat', 'kertas', 1500, 0);

SELECT id, nama, kategori, harga, stok FROM produk ORDER BY id;

DELETE FROM produk
WHERE stok = 0
RETURNING id, nama;

INSERT INTO produk (nama, kategori, harga, stok)
VALUES ('Map Plastik', 'alat tulis', 12000, 25)
RETURNING id, nama, harga;

INSERT INTO produk (nama, kategori, harga, stok)
VALUES ('Kertas Folio', 'kertas', 20000, 50)
RETURNING *;

DELETE FROM produk
WHERE id = 6
RETURNING count(*) AS jumlah_terhapus;

UPDATE produk
SET harga = harga * 1.1
WHERE kategori = 'alat tulis'
RETURNING id, nama, harga AS harga_baru;

UPDATE produk
SET stok = stok + 20
WHERE id = 3
RETURNING id, nama, harga, stok;