Problems Converting Triggers From Oracle PLSQL to PLPGSQL

Started by Nonameover 24 years ago1 messages
#1Noname
joseph.castille@wcom.com

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