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 Headcrusher - Plasma Fire
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
