Received: from lin.local.lan ([216.221.221.2]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2QFfjw18757 for ; Mon, 26 Mar 2001 10:41:45 -0500 (EST) (envelope-from najm@mondo-live.com) Received: from mondo-live.com (IDENT:najm@najm.local2.lan [192.168.2.20]) by lin.local.lan (8.9.3/8.8.7) with ESMTP id PAA04083 for ; Mon, 26 Mar 2001 15:42:34 GMT Message-ID: <3ABF631B.18B66558@mondo-live.com> Date: Mon, 26 Mar 2001 10:41:15 -0500 From: Najm Hashmi X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.2.14-5.0 i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql Subject: is it me or trigger side effects Content-Type: multipart/mixed; boundary="------------89EF674F97FFFF0090543437" X-Archive-Number: 200103/522 X-Sequence-Number: 2192 This is a multi-part message in MIME format. --------------89EF674F97FFFF0090543437 Content-Type: multipart/alternative; boundary="------------5C01C4F711A67721E290C500" --------------5C01C4F711A67721E290C500 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi all, I have written a trigger to update a table called categories whenever a tuple is either deleted or inserted in tables articles, media, and links. It works fine for inserts but "pukes" on deletes :). I am using two auxiliary addcount(varchar) and delecount (varchar) I am getting the following error on deletes: record new is unassigned yet . below is my code. drop function addCount(varchar); create function AddCount(varchar) returns integer as ' declare cat_id alias for $1; len integer; cnt integer; cond varchar; begin len:= length(cat_id); cnt:=0; for i in 1 .. len loop cnt:=cnt +1; cond:=substr(cat_id,1,cnt); update categories set items= items+1 where id like cond; end loop; return cnt; end; ' language 'plpgsql'; select addCount('KDA'); drop function delCount(varchar); create function delCount(varchar) returns integer as ' declare cat_id alias for $1; len integer; cnt integer; cond varchar; begin len:= length(cat_id); cnt:=0; for i in 1 .. len loop cnt:=cnt +1; cond:=substr(cat_id,1,cnt); update categories set items= items-1 where id like cond; end loop; return cnt; end; ' language 'plpgsql'; select delCount('KDA'); drop trigger trigger_update_articles on articles; drop trigger trigger_update_links on links; drop trigger trigger_update_media on media; drop function updateCat(); create function updateCat() returns opaque as ' declare rec record; rename new to cat; rename old to ct; maxlen integer; begin if tg_op = ''INSERT'' and cat.category is null then raise exception ''You are missing entry for category field''; else if cat.display then maxlen:= addCount(cat.category); return cat; else return cat; end if; end if; if tg_op = ''DELETE''then maxlen:= delCount(ct.category); return ct; end if; end; ' language 'plpgsql'; create trigger trigger_update_articles before insert or delete on articles for each row execute procedure updateCat(); create trigger trigger_update_media before insert or delete on media for each row execute procedure updateCat(); create trigger trigger_update_links before insert or delete on links for each row execute procedure updateCat(); --------------5C01C4F711A67721E290C500 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Hi all,
I have written a trigger to update a table called categories whenever a tuple is either deleted or inserted in tables articles, media, and links. It works fine for inserts but "pukes" on deletes :). I am using two auxiliary addcount(varchar) and delecount (varchar)
I am getting the following error on deletes:
 record new is unassigned yet .
below is my code.

drop function addCount(varchar);
create function AddCount(varchar) returns integer as '
declare
    cat_id alias for $1;
    len integer;
    cnt integer;
    cond varchar;

begin
     len:= length(cat_id);
     cnt:=0;
     for i in  1 .. len loop
          cnt:=cnt +1;
          cond:=substr(cat_id,1,cnt);

          update categories set items= items+1 where id like cond;
     end loop;
 

     return cnt;

end;
' language 'plpgsql';

select addCount('KDA');
 
 
 

drop function delCount(varchar);
create function delCount(varchar) returns integer as '
declare
    cat_id alias for $1;
    len integer;
    cnt integer;
    cond varchar;
begin
    len:= length(cat_id);
    cnt:=0;
     for i in  1 .. len  loop
           cnt:=cnt +1;
           cond:=substr(cat_id,1,cnt);
          update categories set items= items-1 where id like cond;

     end loop;
     return cnt;

end;
' language 'plpgsql';

select delCount('KDA');
drop trigger trigger_update_articles on articles;
drop trigger trigger_update_links on links;
drop trigger trigger_update_media on media;
drop function updateCat();

create function updateCat() returns opaque as '
declare
    rec     record;
    rename new to cat;
    rename old to ct;
    maxlen integer;

begin
     if tg_op = ''INSERT''  and cat.category is null then
          raise exception ''You are missing entry for category field'';

     else
          if cat.display then
               maxlen:= addCount(cat.category);
               return cat;
          else
               return cat;
          end if;
     end if;
     if tg_op = ''DELETE''then
          maxlen:= delCount(ct.category);
          return ct;
     end if;

end;
' language 'plpgsql';
create trigger trigger_update_articles before insert or delete
on articles for each row execute procedure updateCat();
create trigger trigger_update_media before  insert or delete
on media for each row execute procedure updateCat();
create trigger trigger_update_links before insert or delete
on links for each row execute procedure updateCat();
 
 
  --------------5C01C4F711A67721E290C500-- --------------89EF674F97FFFF0090543437 Content-Type: text/x-vcard; charset=us-ascii; name="najm.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Najm Hashmi Content-Disposition: attachment; filename="najm.vcf" begin:vcard n:Hashmi;Najm x-mozilla-html:FALSE org:Mondo-Live.com;www.flipr.com adr:;;;;;; version:2.1 email;internet:najm@mondo-live.com x-mozilla-cpt:;6144 fn:Najm Hashmi end:vcard --------------89EF674F97FFFF0090543437--