Problems Converting Triggers From Oracle PLSQL to PLPGSQL

Поиск
Список
Период
Сортировка
От joseph.castille@wcom.com (Joseph Castille)
Тема Problems Converting Triggers From Oracle PLSQL to PLPGSQL
Дата
Msg-id 5849caa3.0108151351.6f3c9810@posting.google.com
обсуждение исходный текст
Список 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


В списке pgsql-hackers по дате отправления:

Предыдущее
От: Peter Moscatt
Дата:
Сообщение: CREATEDB Where ??
Следующее
От: "Walter L. Preuninger II"
Дата:
Сообщение: 7.0.3 on AIX 4.2.1.0.06