Problems Converting Triggers From Oracle PLSQL to PLPGSQL

From: joseph(dot)castille(at)wcom(dot)com (Joseph Castille)
To: pgsql-hackers(at)postgresql(dot)org
Subject: Problems Converting Triggers From Oracle PLSQL to PLPGSQL
Date: 2001-08-15 21:51:04
Message-ID: 5849caa3.0108151351.6f3c9810@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2001-08-15 21:56:45 Re: Dollar in identifiers
Previous Message Tom Lane 2001-08-15 21:46:47 Re: PostGIS spatial extensions