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;

On 3/2/06, Jonah H. Harris <jonah(dot)harris(at)gmail(dot)com> wrote:
>
> All,
>
> This is only the current patch updated to apply cleanly on cvs... it's not
> ready for -patches yet as I still haven't spent much time looking through it
> and testing it. This is just for anyone to play with and find issues.
>
> My focus for the next couple days is on getting INS/UPD/DEL RETURNING
> ready for 8.2... all comments, suggestions, issues would be appreciated.
>
>
> --
> Jonah H. Harris, Database Internals Architect
> EnterpriseDB Corporation
> 732.331.1324
>
>

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324