ERROR: fmgr_info: function 24809: cache lookup failed
Hi all. I feel awfully stupid.
Migrating my skillset from Oracle to Postgres, I am trying (in vain) to
create a trigger and a sequence to "auto increment" the primary key in a
table, so I won't have to worry about setting it; each time you INSERT
into the table, the primary key will be automagically assigned.
Problem is, it isn't working... I get "cache lookup failed" whenever I try
to INSERT on that table!
THE TABLE:
CREATE TABLE SENTENCES (
SENTENCEID NUMERIC,
CHAPTERID NUMERIC,
SOFFSET INTEGER,
PRIMARY KEY (SENTENCEID)
);
THE SEQUENCE:
CREATE SEQUENCE sentenceid_seq;
THE FUNCTION:
CREATE FUNCTION sentenceid_fcn()
RETURNS OPAQUE AS '
BEGIN
SELECT nextval(sentenceid_seq)
INTO :new.sentenceid
FROM dual;
END; ' language plpgsql;
THE TRIGGER:
CREATE TRIGGER sentenceid_trig
BEFORE INSERT
ON sentences
FOR EACH ROW
EXECUTE PROCEDURE sentenceid_fcn();
END;
And when I try to INSERT into SENTENCES, without my SENTENCEID, hoping
that the function/trigger/sequence will kick in and assign it for me...
architxts=# INSERT INTO SENTENCES (CHAPTERID, SOFFSET) VALUES (123, 147);
ERROR: fmgr_info: function 24809: cache lookup failed
I'm certain I'm doing SOMETHING terribly wrong. Could someone help?
PLEEEEASE? Why am I getting that error?
Many thanks in advance!
--
J e s s i c a L e a h B l a n k
You can do without function/trigger, try this :
Create a sequence first :
CREATE SEQUENCE sentenceid_seq;
Create the table:
CREATE TABLE SENTENCES (
SENTENCEID integer DEFAULT NEXTVAL('sentenceid_seq'::text) NOT NULL,
CHAPTERID NUMERIC,
SOFFSET INTEGER,
PRIMARY KEY (SENTENCEID)
);
Besides, I am not sure if DUAL exists in PostgreSQL.
Tariq Muhammad
Liberty RMS
tariq@libertyrms.info
v:416-646-3304 x 111
c:416-993-1859
p:416-381-1457
On Tue, 17 Dec 2002, Jessica Blank wrote:
Show quoted text
Hi all. I feel awfully stupid.
Migrating my skillset from Oracle to Postgres, I am trying (in vain) to
create a trigger and a sequence to "auto increment" the primary key in a
table, so I won't have to worry about setting it; each time you INSERT
into the table, the primary key will be automagically assigned.Problem is, it isn't working... I get "cache lookup failed" whenever I try
to INSERT on that table!THE TABLE:
CREATE TABLE SENTENCES (
SENTENCEID NUMERIC,
CHAPTERID NUMERIC,
SOFFSET INTEGER,
PRIMARY KEY (SENTENCEID)
);THE SEQUENCE:
CREATE SEQUENCE sentenceid_seq;THE FUNCTION:
CREATE FUNCTION sentenceid_fcn()
RETURNS OPAQUE AS '
BEGIN
SELECT nextval(sentenceid_seq)
INTO :new.sentenceid
FROM dual;
END; ' language plpgsql;THE TRIGGER:
CREATE TRIGGER sentenceid_trig
BEFORE INSERT
ON sentences
FOR EACH ROW
EXECUTE PROCEDURE sentenceid_fcn();
END;And when I try to INSERT into SENTENCES, without my SENTENCEID, hoping
that the function/trigger/sequence will kick in and assign it for me...architxts=# INSERT INTO SENTENCES (CHAPTERID, SOFFSET) VALUES (123, 147);
ERROR: fmgr_info: function 24809: cache lookup failedI'm certain I'm doing SOMETHING terribly wrong. Could someone help?
PLEEEEASE? Why am I getting that error?Many thanks in advance!
--
J e s s i c a L e a h B l a n k---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org