Implementation of Oracle Flashback like functionality in PostgreSQL.

gistfile1.txt

-- TODO update start_time

drop table if exists test_current cascade;

create table test_current(
  -- TODO - store lsn instead of dates?
	-- flashback columns
	start_date abstime not null default now(), 
	stop_date abstime not null default 'infinity',
	-- user columns
	id integer, 
	txt text
);

create table test_archived(
	modification char(1) not null,
	check(stop_date != 'infinity')
) inherits (test_current);

create or replace function archive() returns trigger as $$
declare 
	arch test_archived;
begin
	arch = old;
	arch.stop_date = now();
	if(TG_OP = 'DELETE') then
		arch.modification = 'D';
	elsif (TG_OP = 'UPDATE') then
		arch.modification = 'U';
	end if;
	insert into test_archived select (arch.*);
	return null;
end;
$$ language plpgsql;        

-- You must replan all your queries after calling this function.
-- Alternatively you can create new session and call this function before
-- preparing any queries.
create or replace function set_flashback(abstime) returns void as $$
begin
	drop table if exists pg_flashback;
	create temp table pg_flashback(flashback_date abstime);
	insert into pg_flashback values($1);
end;
$$ language plpgsql;

create or replace function get_flashback(result out abstime) returns abstime as $$
begin
select flashback_date into result from pg_flashback;
end;
$$ language plpgsql immutable;

create trigger archive
after delete or update on test_current
for each row execute procedure archive();

create view test as 
(
	select distinct on (id) * from test_current
	where stop_date > get_flashback() and start_date <= get_flashback()
	and get_flashback() is not null
	order by id,stop_date asc
)
union all
(
	select * from test_current where stop_date='infinity' and get_flashback() is null
);

create or replace rule insert_test as on insert to test
do instead insert into test_current(id,txt) select new.id,new.txt;

create or replace rule delete_test as on delete to test
do instead delete from test_current where id = old.id;

create or replace rule update_test as on update to test
do instead update test_current set
txt = new.txt
where id = new.id;

--test case

insert into test(id,txt) values(1,'foo');
insert into test(id,txt) values(2,'bar');
delete from test where id = 2;
update test set txt = 'baz' where id = 1;