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
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 |