Re: [GENERAL] PL/PGSQL

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