Thread: PL/pgSQL syntax help?

Hi,
I would like to know how to convert the following
Oracle syntax to postgreSQL using PL/pgSQL.
----------------------
create trigger mailing_list_reg_date
before insert on mailing_list
for each row
when (new.reg_date is null)
begin
  :new.reg_date := sysdate;
end;
---------------------

Here is what I did(which obviously doesn't work. :-( ).

-----------
create function reg_yes () returns opaque as
 '
begin
 if new.reg_date isnull then
 new.reg_date := ''now'';
endif;
end;
' language 'plpgsql';
------------

and

----------
create trigger mailing_list_reg_date
before insert on mailing_list
for each row
execute procedure reg_yes();
-----------

Both step went OK until I tried to test by inserting into
mailing_list without reg_date.

 db=> insert into mailing_list ...

ERROR  plpgsql: cache lookup for proc 163937 failed.

That is all my story. Sorry for the long post.
(ver: 7.0.2 on solaris 2.7)

Thank you

Jinsoo

Jinsoo Hwang <jshwang@anova.inha.ac.kr> writes:
> ERROR  plpgsql: cache lookup for proc 163937 failed.

I suspect that you deleted and recreated the function after
creating the trigger.  Currently you must recreate the trigger
after you recreate the function --- but the system won't tell
you so in any direct fashion.  Annoying, isn't it?

            regards, tom lane