By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,933 Members | 1,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,933 IT Pros & Developers. It's quick & easy.

Triggers/perform oddity between 7.2 and 7.3

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.