We're trying to migrate from Oracle to Postgres and I've been having
problems converting the procedural language stuff.  I've looked at the
web documentation and my functions/triggers seem like they should
work.  What am I doing wrong?  Any help you could give me would be
greatly appreciated.  I know I must be missing something, but I can't
figure out what it is.


Running this query:

insert into EXTRANET_SECTION (ID, section_name, parent, extranetname)
values (255,' Main',0, 'test');


Gives me this error:

fmgr_info: function 19464: cache lookup failed



These are the triggers/functions and the table they access:


drop function increment_section();

create function increment_section()
returns opaque
as 'BEGIN
  DECLARE
    x integer;
 BEGIN
  SELECT COUNT(*) INTO x
  FROM EXTRANET_ids
  WHERE extranetname = :NEW.extranetname;
 IF x = 0
  then insert into EXTRANET_ids (extranetname, EXTRANET_section_id,
EXTRANET_docs_id) values (:NEW.extranetname, 0, 0);
 END IF;
  update EXTRANET_ids
    set EXTRANET_section_id = EXTRANET_section_id +1
  WHERE extranetname = :NEW.extranetname;
  select EXTRANET_section_id INTO :NEW.ID from EXTRANET_ids where
extranetname = :NEW.extranetname;
  return NEW;
END;'
language 'plpgsql';


Drop trigger ins_EXTRANET_section on EXTRANET_section;

CREATE TRIGGER ins_EXTRANET_section
  BEFORE INSERT ON EXTRANET_section
  FOR EACH ROW
  execute procedure increment_section();



TABLES THIS TRIGGER ACCESSES:


create table EXTRANET_ids
(extranetname varchar(40) NOT NULL primary key,
 EXTRANET_section_id int NOT NULL,
 EXTRANET_docs_id int NOT NULL);




Thanks for your help,
Joseph

15 Aug 2001 14:51:04 -0700
joseph.castille@wcom.com (Joseph Castille)
Problems Converting Triggers From Oracle PLSQL to PLPGSQL