trigger: NEW/OLD-error or nothing happens

From: Knut Suebert <knut(dot)suebert(at)web(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: trigger: NEW/OLD-error or nothing happens
Date: 2001-07-07 21:23:42
Message-ID: 20010707232342.A9030@cascal.vtb
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I'm trying to anaylze a net-acct-log.

so:

create table traf ( id serial primary key, time timestamp, proto int2,
sip inet, sport int4, dip inet, dport int4, size int4, device text,
minport int4, maxport int4);

minport is to group by the service, it (and maxport for completeness)
should be filled by

create function nacmin(int4,int4) returns int as'
begin
if $1<$2 then
return $1;
end if;
return $2;
end;
' language 'plpgsql';

create function nacmax(int4,int4) returns int as'
begin
if $1>$2 then
return $1;
end if;
return $2;
end;
' language 'plpgsql';

these functions work.

the automatic execution should be done by a function with a stupid name:

create function nac_viceversa() returns opaque as'
begin
NEW.minport := nacmin(OLD.sport,OLD.dport);
NEW.maxport := nacmax(OLD.sport,OLD.dport);
return NEW;
end;
'language 'plpgsql';

create trigger nac_update after insert or update on traf
for each row execute procedure nac_viceversa();

but than

ERROR: record old is unassigned yet

if i take NEW. instead of OLD.: no errors -- but minport and maxport
are empty after insert. (could it be that OLD makes no sense on
insert? I'd lie if I'd say that I understood the conecpt, but
databases are hard stuff for me even in my native language)

I searched documentation and archives[1], tried some tricks after
'DECLARE', tried with and without OLD and NEW, but all i get are
errors or nothinng happens.

Thanks a lot for your help,
Knut Suebert

[1] everything found was in -general or -hackers. So I decided to send
this to -general and not to -novice, sorry if i'm wrong.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joe Conway 2001-07-07 21:52:01 Re: trigger: NEW/OLD-error or nothing happens
Previous Message Laurent HERVE 2001-07-07 21:10:56 Set priority for requests