From: | "Jonah H(dot) Harris" <jonah(dot)harris(at)gmail(dot)com> |
---|---|
To: | pgsql-patches(at)postgresql(dot)org |
Subject: | INS/UPD/DEL RETURNING for 8.2 |
Date: | 2006-03-02 22:51:20 |
Message-ID: | 36e682920603021451n1b086c6fu636b43faef70f7b4@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-patches |
Patch for core and PL/pgSQL to support the INSERT/UPDATE/DELETE RETURNING
syntax in 8.2
INSERT/UPDATE/DELETE seem to work fine in normal operation but there is an
error with DELETE RETURNING when used through PL/pgSQL.
Here's an example PL/pgSQL test:
CREATE SEQUENCE test_id_seq START 1 INCREMENT 1;
CREATE TABLE test_tbl (
test_id BIGINT NOT NULL
DEFAULT nextval('test_id_seq'),
test_name VARCHAR(64) NOT NULL,
PRIMARY KEY (test_id));
CREATE OR REPLACE FUNCTION test_func (test_nm VARCHAR)
RETURNS VOID AS $$
DECLARE
current_rec RECORD;
BEGIN
-- Test INSERT RETURNING
INSERT INTO test_tbl (test_name) VALUES (test_nm)
RETURNING * INTO current_rec;
RAISE NOTICE 'test_id is %', current_rec.test_id;
RAISE NOTICE 'test_name is %', current_rec.test_name;
-- Test UPDATE RETURNING
UPDATE test_tbl SET test_name = 'Uncle Bob'
WHERE test_id = current_rec.test_id
RETURNING * INTO current_rec;
RAISE NOTICE 'test_id is %', current_rec.test_id;
RAISE NOTICE 'test_name is %', current_rec.test_name;
-- Test DELETE RETURNING
DELETE FROM test_tbl WHERE test_id = current_rec.test_id
RETURNING * INTO current_rec;
-- This DOES NOT WORK
RAISE NOTICE 'test_id is %', current_rec.test_id;
RAISE NOTICE 'test_name is %', current_rec.test_name;
RETURN;
END;
$$ LANGUAGE plpgsql;
--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324
Attachment | Content-Type | Size |
---|---|---|
pg82-iudret.patch | text/x-patch | 37.2 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Neil Conway | 2006-03-02 23:45:38 | Re: INS/UPD/DEL RETURNING for 8.2 |
Previous Message | Tom Lane | 2006-03-02 22:04:06 | Re: Scanning for insert |