[GENERAL] Update columns in same table from update trigger?

[Pablo Romero Abiti]

Here's what I want to do:

I have a master table that has 2 columns: idcol1 and idcol2, where idcol2 is 
equivalent to idcol1


Table: color_eq

idcol1  idcol2

1          1
2          2
2          3

Table: warehouse
idcol    qty
1        10
2        20


if I execute "update warehouse set qty=10 where idcolor=3", I want the trigger 
to search table color_eq for idcol2=3, picks its corresponding idcol1 and 
update the table warehouse with idcol1.


The problem I'm facing is that the trigger before update won't execute if there 
isn't a row with idcol=3 in the table warehouse.


Here's my code:

CREATE OR REPLACE FUNCTION update_warehouse() returns "trigger" AS '
   declare idcolmaestro float:=0;
   BEGIN
      select into a idcolor1 from color_eq where idcolor2=old.idcolor;
      if a is null then 
         a=old.idcolor;
      end if;

      new.idcolor=a;
      return new;
  END;
' LANGUAGE 'plpgsql' VOLATILE;

  CREATE TRIGGER update_warehouse_trigger
  before UPDATE
  ON warehouse
  FOR EACH ROW
  EXECUTE PROCEDURE update_warehouse();

Any help would be greatly appreciated!