Skip site navigation (1) Skip section navigation (2)

INS/UPD/DEL RETURNING for 8.2

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 (view raw or flat)
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: pg82-iudret.patch
Description: text/x-patch (37.2 KB)

Responses

pgsql-patches by date

Next:From: Neil ConwayDate: 2006-03-02 23:45:38
Subject: Re: INS/UPD/DEL RETURNING for 8.2
Previous:From: Tom LaneDate: 2006-03-02 22:04:06
Subject: Re: Scanning for insert

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group