Hi gurus,
Anyone knows how to solve this? I was playing around with triggers on
my own machine which runs 7.3 and there it works okey, but when trying
it on 7.2 I get an error about duplicate keys. Upgrading to 7.3 on the
other machines is not possible yet. Any help on getting it to work
with 7.2 would be appreciated.
<snip>
create table a(
n varchar,
x int,
y int,
z int
);
create table b(
n varchar,
x_sum int,
y_sum int,
z_sum int,
primary key(n)
);
create function plpgsql_call_handler() returns language_handler as
'/usr/lib/postgresql/lib/plpgsql' language c;
create language plpgsql handler plpgsql_call_handler;
create function "a_trigger"() returns "trigger" as '
declare n_rows integer;
begin
if (tg_op = ''INSERT'') then
perform n from b where n = new.n; -- Problem is here
if not found then -- I think..
insert into b (
n,
x_sum,
y_sum,
z_sum
) values (
new.n,
new.x,
new.y,
new.z
);
else
update b set
x_sum = x_sum + new.x,
y_sum = y_sum + new.y,
z_sum = z_sum + new.z
where n = new.n;
end if;
return new;
end if;
if (tg_op = ''DELETE'') then
perform n from a where n = old.n;
get diagnostics n_rows = ROW_COUNT;
if n_rows = 1 then
delete from b where n = old.n;
else
update b set
x_sum = x_sum - old.x,
y_sum = y_sum - old.y,
z_sum = z_sum - old.z
where n = old.n;
end if;
return old;
end if;
if (tg_op = ''UPDATE'') then
update b set
x_sum = x_sum - old.x + new.x,
y_sum = y_sum - old.y + new.y,
z_sum = z_sum - old.z + new.z
where n = new.n;
return new;
end if;
end
' language 'plpgsql';
create trigger a_trigger before insert or update or delete on a for
each row execute procedure a_trigger();
</snap>
In 7.2.1 I get error:
trigger_test=# insert into a values('a', 1, 2, 3);
INSERT 49555 1
trigger_test=# insert into a values('a', 1, 2, 3);
NOTICE: Error occurred while executing PL/pgSQL function a_trigger
NOTICE: line 6 at SQL statement
ERROR: Cannot insert a duplicate key into unique index b_pkey
trigger_test=# select version();
version
---------------------------------------------------------------
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)
In 7.3.3 it works fine:
trigger_test=# insert into a values('a', 1, 2, 3);
INSERT 456365 1
trigger_test=# insert into a values('a', 1, 2, 3);
INSERT 456366 1
trigger_test=# select version();
version
-------------------------------------------------------------------------------
PostgreSQL 7.3.3 on i386-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3
(Debian)
(1 row)
Thanks.
--
Riddler & Headcrucher - Plasma Fire
