plpgsql - Using Exception handling to fix foreign key constraint in PL/pgsql (PostgreSQL) -

trying learn exception handling in pgsql (postgresql 9.1). following sp fails with

error: insert or update on table "dx" violates foreign key constraint "fk_icd9" sql state: 23503 detail: key (cicd9, cdesc)=(244.9, testing1) not present in table "icd9". 

fk_icd9 defined table dx as:

constraint fk_icd9 foreign key (cicd9, cdesc)   references icd9 (cicd9, cdesc) match simple   on update cascade on delete restrict deferrable deferred 

my attempt @ sp is:

create or replace function g_test() returns void $$ declare     r view_dx%rowtype; begin   r.cicd9 := '244.9';   r.groupid := 'bbbb      cccc        199971230';     r.tposted := '2013-08-30 17:45:45'::timestamp;   r.cdesc := 'testing1';    loop     begin       update dx set cdesc = r.cdesc       cicd9 = r.cicd9 , groupid = r.groupid , tposted = r.tposted;     exception        when others         insert icd9(cicd9, cdesc) values (r.cicd9, r.cdesc);     end;     if found       return;     end if;    end loop; end; $$ language plpgsql; 

i trying update table, dx, has foreign key constraint in second table, icd9. if update of dx table fails because of constraint, insert new record in parent icd9 table loop first table, dx, update.

what doing wrong? how done?

edit #1: editing code shown below to:

 create or replace function g_savedx3() returns void $$  declare  _cicd9 character varying(8); _groupid character varying(33); _tposted timestamp without time zone; _cdesc character varying(80);    begin _cicd9 := '244.9'; _groupid := 'bbbbb        aaaaa        199998';   _tposted := '2013-08-30 17:45:45'::timestamp; _cdesc := 'testing109';  loop     begin         raise notice 'about update ';          update dx set cdesc = _cdesc                              cicd9 = _cicd9 ,                  groupid = _groupid , tposted = _tposted;          raise notice 'updated in g_savedx3';          if found             return;         end if;           exception              when others                 raise notice 'in exception g_savedx3, insert';                  insert icd9(cicd9,cdesc) values (_cicd9, _cdesc);                  raise notice 'in exception inserted';     end; end loop; end; $$ language plpgsql;  select g_savedx3(); 

yields following messages:

notice: update notice: updated in g_savedx3

error: insert or update on table "dx" violates foreign key constraint "fk_icd9" detail: key (cicd9, cdesc)=(244.9, testing109) not present in table "icd9". ********** error **********

error: insert or update on table "dx" violates foreign key constraint "fk_icd9" sql state: 23503 detail: key (cicd9, cdesc)=(244.9, testing109) not present in table "icd9".

note: found old entry on updates violating foreign constraints tom lane (2004)

yes ... you're expecting ri triggers fire during plpgsql function, in fact fire @ completion of outer statement called plpgsql function.

there's been debate whether desirable behavior, that's how @ moment.

if still case, might explain problem. ideas how fix code? (it should have worked??) thanks.

***as mentioned below, i'm guessing default behavior which, in case, causes exception called after completion of plpgsql function. behavior can changed (postgresql 9.1) with:

set constraints immediate;

which needed make work

if has bearing, here definition of icd9 table:

 create table icd9 (  recid serial not null,  cicd9 character varying(8),  cdesc character varying(80) not null,  "timestamp" timestamp without time zone default now(),  modified timestamp without time zone default now(),  chronic boolean not null default false,  constraint pk_icd9_recid primary key (recid),  constraint constraint_cdesc unique (cicd9, cdesc),  constraint desccheck check (cdesc::text <> ''::text) ) (  oids=false ); 

switch old-fashioned debugging. here code, insert.

create or replace function f () returns void  $$ declare newval integer :=3 ; begin loop begin     raise notice 'about update ';     update b set id2 = newval;     raise notice 'updated ';     if found       return;     end if; exception      when others     raise notice 'in exception , insert';       insert values (newval); raise notice 'in exception inserted'; end; end loop; end; $$ language plpgsql; 

execution:

select f(); notice:  update  notice:  in exception , insert notice:  in exception inserted notice:  update  notice:  updated  

table definitions:

test=# \d+                            table "w2gi.a"  column |  type   | modifiers | storage | stats target | description  --------+---------+-----------+---------+--------------+-------------  id     | integer | not null  | plain   |              |  indexes:     "a_pkey" primary key, btree (id) referenced by:     table "b" constraint "b_id2_fkey" foreign key (id2) references a(id) has oids: no test=# \d+ b                            table "w2gi.b"  column |  type   | modifiers | storage | stats target | description  --------+---------+-----------+---------+--------------+-------------  id1    | integer |           | plain   |              |   id2    | integer |           | plain   |              |  foreign-key constraints:     "b_id2_fkey" foreign key (id2) references a(id) has oids: no