Thread: PL/PGSQL
Hi pgsql list-members I'm new at this list an want some answers for the following problem: On every time, when my trigger is called I get the Message: ERROR: fmgr_info: function 87776 : cache lookup failure What means this - or where are described ERROR-Messages at the Documentation. What I've done: create table test1 (x int2, free bool); create table history (x int2); create function insert_history () returns opaque as 'select x into history from test1 where free=\'t\';' language 'plpgsql'; create trigger ins1 after update on test1 for each row execute procedure insert_history(); by and thanks Jens GEK CONSULTING GmbH An den Teichen 5 09224 Mittelbach Tel.: (0371) 80 88 260 Fax.: (0371) 80 88 266 EMail: J.Felber@gek-consulting.de jfe@gek-online.de
At 10:17 +0300 on 23/08/1999, Jens Felber wrote: > On every time, when my trigger is called I get > the Message: > > ERROR: fmgr_info: function 87776 : cache lookup failure > > What means this - or where are described ERROR-Messages at the Documentation. > > What I've done: > > create table test1 (x int2, free bool); > create table history (x > int2); > > create function insert_history () returns opaque as > 'select x into history from test1 where free=\'t\';' > language 'plpgsql'; > > create trigger ins1 after update on test1 for each row execute procedure > insert_history(); I'm not sure the above is a legal plpgsql procedure. Looks more like sql than anything procedural. One thing I'm sure is that it isn't what you wanted to do. SELECT INTO creates a new table, so if the table exists, it won't work. The way to populate an existing table with values from another table is INSERT INTO history SELECT x FROM test1 WHERE free; (Note that you don't have to compare booleans... It's redundant. You can just use them directly. But that's just a side comment. Saves you on those escaped quotes). Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
Hi Jens. First of all "select ... into ..." creates a new table and you can't use existing table after "into". And second - syntax errors in your function. So, you must do something like: create function insert_history () returns opaque as ' declare rec record; begin for select x into rec from test1 where free=\'t\' loop insert into history (field1, field2, ... ) values (rec.field1, rec.field2, ...); end loop; end; ' language 'plpgsql'; Or consider a rule for soving this task. Sincerely yours, Yury. don.web-page.net, ICQ 11831432 Jens Felber wrote: > > Hi pgsql list-members > > I'm new at this list an want some answers for the following problem: > > On every time, when my trigger is called I get > the Message: > > ERROR: fmgr_info: function 87776 : cache lookup failure > > What means this - or where are described ERROR-Messages at the Documentation. > > What I've done: > > create table test1 (x int2, free bool); > create table history (x > int2); > > create function insert_history () returns opaque as > 'select x into history from test1 where free=\'t\';' > language 'plpgsql'; > > create trigger ins1 after update on test1 for each row execute procedure > insert_history(); > > by and thanks > Jens > > GEK CONSULTING GmbH > An den Teichen 5 > 09224 Mittelbach > > Tel.: (0371) 80 88 260 > Fax.: (0371) 80 88 266 > EMail: J.Felber@gek-consulting.de > jfe@gek-online.de > > ************
At 16:15 23.08.99 +0600, you wrote: >Hi Jens. > >First of all "select ... into ..." creates a new table and you can't use >existing table after "into". >And second - syntax errors in your function. >So, you must do something like: > >create function insert_history () returns opaque as ' >declare > rec record; >begin > for select x into rec from test1 where free=\'t\' loop > insert into history (field1, field2, ... ) > values (rec.field1, rec.field2, ...); > end loop; >end; >' language 'plpgsql'; Hi Yury, Hi Herouth thanks for your answers but the error-message exist further: ERROR: fmgr_info: function xxxxx : cache lookup failure (xxxx is the function oid) At the Postgres Error-Log-File ist nothing more to be seen then that. I believe, the problem exists outside of the implementation of creating functions and triggers. But I dont know, how I can kill the error - mistake, failure configuration? (however) by Jens
At 14:13 +0300 on 23/08/1999, Jens Felber wrote: > thanks for your answers but the error-message exist further: > > ERROR: fmgr_info: function xxxxx : cache lookup failure > (xxxx is the function oid) > > At the Postgres Error-Log-File ist nothing more to be seen then that. > I believe, the problem exists outside of the implementation of creating > functions > and triggers. > But I dont know, how I can kill the error - mistake, failure configuration? Sounds to me that you have created the trigger with some version of the function, and then you redefined the function, dropped the old one, or some such. Once this is done, the trigger will give you that error message, since it works by oid and not the function name. Try to drop the trigger, if you haven't already. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
At 14:46 23.08.99 +0300, Herouth Maoz wrote: >At 14:13 +0300 on 23/08/1999, Jens Felber wrote: > > >Sounds to me that you have created the trigger with some version of the >function, and then you redefined the function, dropped the old one, or some >such. Once this is done, the trigger will give you that error message, >since it works by oid and not the function name. > >Try to drop the trigger, if you haven't already. Hi folks, the problem is solved - thanks for all hints. Like Herouth said - I've droped the function and create an new version. But the trigger was the old version. After a drop of both and newcreation - it works fine. Thanks a lot. Jens