467,210 Members | 1,298 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,210 developers. It's quick & easy.

Triggers/perform oddity between 7.2 and 7.3

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
Jul 19 '05 #1
  • viewed: 2136
Share:
1 Reply
an*****@sparcy.net (Andreas Forsgren) wrote in message news:<a4**************************@posting.google. com>...
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.

[...]
if (tg_op = ''INSERT'') then
perform n from b where n = new.n; -- Problem is here
if not found then -- I think..


if (tg_op = ''INSERT'') then
perform n from b where n = new.n; -- Works in
get diagnostics n_rows = ROW_COUNT; -- 7.2
if n_rows = 0 then

[...]

Replying to my own post, but I got it to work now.

--
Covenant - Stalker
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Keith | last post: by
11 posts views Thread by raulgz | last post: by
1 post views Thread by Andreas Forsgren | last post: by
4 posts views Thread by Re_endian_miss@yahoo.com | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.