CREATE TABLE test(col TEXT);

CREATE OR REPLACE FUNCTION sp_test_delete()
RETURNS trigger AS '
DECLARE
BEGIN
    INSERT INTO temp_test (col) VALUES (OLD.col);

    RAISE NOTICE ''Inserting: col=%'', OLD.col;

    RETURN NEW;
END; ' language 'plpgsql';

CREATE TRIGGER tr_test_delete AFTER DELETE ON test
            FOR EACH ROW EXECUTE PROCEDURE sp_test_delete();

CREATE OR REPLACE FUNCTION flush_test()
RETURNS VOID AS '
DECLARE
    rec_debug RECORD;
BEGIN

    BEGIN
        CREATE TEMPORARY TABLE temp_test (col TEXT) ON COMMIT DELETE ROWS;
    EXCEPTION
        WHEN duplicate_table THEN
    END;

    -- delete processed
    DELETE FROM test;

    FOR rec_debug IN
        SELECT * FROM temp_test
    LOOP
        RAISE NOTICE ''Row in temp_test: col=%'', rec_debug.col;
    END LOOP;
    
    RETURN;
    
END;
' LANGUAGE plpgsql;

INSERT INTO test VALUES ('1');
SELECT flush_test();
INSERT INTO test VALUES ('2');
SELECT flush_test();
INSERT INTO test VALUES ('3');
SELECT flush_test();
INSERT INTO test VALUES ('4');
SELECT flush_test();
INSERT INTO test VALUES ('5');
SELECT flush_test();


*** 8.1.0 behavior ***

cnagy=> INSERT INTO test VALUES ('1');
INSERT 0 1
cnagy=> SELECT flush_test();
NOTICE:  Inserting: col=1
CONTEXT:  SQL statement "DELETE FROM test"
PL/pgSQL function "flush_test" line 12 at SQL statement
 flush_test
------------
  
(1 row)
 
cnagy=> INSERT INTO test VALUES ('2');
INSERT 0 1
cnagy=> SELECT flush_test();
NOTICE:  Inserting: col=2
CONTEXT:  SQL statement "DELETE FROM test"
PL/pgSQL function "flush_test" line 12 at SQL statement
 flush_test
------------
  
(1 row)
 
cnagy=> INSERT INTO test VALUES ('3');
INSERT 0 1
cnagy=> SELECT flush_test();
NOTICE:  Inserting: col=3
CONTEXT:  SQL statement "DELETE FROM test"
PL/pgSQL function "flush_test" line 12 at SQL statement
 flush_test
------------
  
(1 row)
 
cnagy=> INSERT INTO test VALUES ('4');
INSERT 0 1
cnagy=> SELECT flush_test();
NOTICE:  Inserting: col=4
CONTEXT:  SQL statement "DELETE FROM test"
PL/pgSQL function "flush_test" line 12 at SQL statement
 flush_test
------------
  
(1 row)
 
cnagy=> INSERT INTO test VALUES ('5');
INSERT 0 1
cnagy=> SELECT flush_test();
NOTICE:  Inserting: col=5
CONTEXT:  SQL statement "DELETE FROM test"
PL/pgSQL function "flush_test" line 12 at SQL statement
 flush_test
------------
  
(1 row)



*** 8.0.3 behavior ***

cnagy=> INSERT INTO test VALUES ('1');
INSERT 1216290363 1
cnagy=> SELECT flush_test();
NOTICE:  Inserting: col=1
CONTEXT:  SQL statement "DELETE FROM test"
PL/pgSQL function "flush_test" line 12 at SQL statement
NOTICE:  Row in temp_test: col=1
 flush_test
------------
  
(1 row)
 
cnagy=> INSERT INTO test VALUES ('2');
INSERT 1216290370 1
cnagy=> SELECT flush_test();
NOTICE:  Inserting: col=2
CONTEXT:  SQL statement "DELETE FROM test"
PL/pgSQL function "flush_test" line 12 at SQL statement
NOTICE:  Row in temp_test: col=2
 flush_test
------------
  
(1 row)
 
cnagy=> INSERT INTO test VALUES ('3');
INSERT 1216290372 1
cnagy=> SELECT flush_test();
NOTICE:  Inserting: col=3
CONTEXT:  SQL statement "DELETE FROM test"
PL/pgSQL function "flush_test" line 12 at SQL statement
NOTICE:  Row in temp_test: col=3
 flush_test
------------
  
(1 row)
 
cnagy=> INSERT INTO test VALUES ('4');
INSERT 1216290374 1
cnagy=> SELECT flush_test();
NOTICE:  Inserting: col=4
CONTEXT:  SQL statement "DELETE FROM test"
PL/pgSQL function "flush_test" line 12 at SQL statement
NOTICE:  Row in temp_test: col=4
 flush_test
------------
  
(1 row)
 
cnagy=> INSERT INTO test VALUES ('5');
INSERT 1216290376 1
cnagy=> SELECT flush_test();
NOTICE:  Inserting: col=5
CONTEXT:  SQL statement "DELETE FROM test"
PL/pgSQL function "flush_test" line 12 at SQL statement
NOTICE:  Row in temp_test: col=5
 flush_test
------------
  
(1 row)