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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
