Hello everybody,
I'm transferring a table within a database to another table. The first
one has raw data in it, the target table is an actual table with
constraints, foreign key references etc. I use some pl/pgsql code to
transfer the data. Something like this (where target_tbl.country has a
foreign key reference)
create or replace function my_test() returns integer as '
declare
SRC record;
begin
for SRC in select code,country from source_tbl
where code like ''blah%'' loop
update target_tbl set country=SRC.country where code=SRC.code;
end loop;
return 0;
end; '
language 'plpgsql';
select my_test();
drop function my_test();
This works just fine, but... When after hours of updating an invalid
value is inserted into target_tbl.country, the whole thing is called
off, because the FOR ... LOOP behaves like a single transaction. I
would like ths singel UPDATE to fail, nut after that, have the program
continue LOOPing. I enclosed the UPDATE between BEGIN WORK and COMMIT
WORK, but this gives a parse error (nested transactions won't work).
Setting AUTOCOMMIT to off won't work either. How can I solve this,
will I have to use a CURSOR? The DBMS is PostgreSQL 7.2.4.
TIA for any tips!