Problems Converting Triggers From Oracle PLSQL to PLPGSQL
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