Обсуждение: 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