PL/PGSQL

Hi Jens,

You have this kind of message, when you have changed your function
insert_history() without drop-ing and recreating your trigger.

When you change you function, your function get another OID and the trigger
still get the old one who doesn't more exist.

I hope that my english is not to french.......Sorry :-)

Best Regards,
Stephane FILLON.

-----Message d'origine-----
De : Jens Felber <jfe@gek-online.de>
� : pgsql-general@postgreSQL.org <pgsql-general@postgreSQL.org>
Date : lundi 23 ao�t 1999 18:40
Objet : [GENERAL] 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.

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:

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