L.S.
I noticed that after creating these necessary objects:
================================================
create table t_src(value int);
create table t_dest(value int primary key);
create or replace function tr_t_dest_before_iud()
returns trigger
language 'plpgsql'
volatile
strict
security invoker
AS ' declare
begin
RAISE NOTICE ''tr_t_dest_before_iud() triggered for value (%)'', NEW.value;
NEW.value = NEW.value + 1;
RETURN NEW;
END;';
create trigger t_dest_before before insert or update or delete on t_dest for
each row execute procedure tr_t_dest_before_iud();
insert into t_src values (1);
insert into t_src values (5);
insert into t_src values (9);
insert into t_src values (5);
================================================
The following statement causes an error due to the fact that the distinct
isn't producing distinct values anymore.
db=# insert into t_dest select distinct value from t_src;
NOTICE: tr_t_dest_before_iud() triggered for value (1)
NOTICE: tr_t_dest_before_iud() triggered for value (5)
NOTICE: tr_t_dest_before_iud() triggered for value (5)
ERROR: duplicate key violates unique constraint "t_dest_pkey"
This seems to be caused by the update of 'value' in the before-trigger.
Removing the update will let the distinct produce proper results.
4cleanup:
================================================
drop table t_dest;
drop table t_src;
drop function tr_t_dest_before_iud();
================================================
select version();
version
------------------------------------------------------------------------
PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.3
(1 row)
--
Best,
Frank.
