Re: INS/UPD/DEL Returning Patch

Here's my very first proof-of-concept patch to PL/pgSQL to use the RETURNING syntax... INSERT/UPDATE seem to work fine but I think I've found an error with DELETE RETURNING though, so it doesn't work properly just yet.  Give this a test if you get a chance.

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