Thread: psql , stored procedure, triggers and errors

Hi,

I am new to this mailing list. Sorry if my question sounds silly.

I have compiled postgres 7.0.3 from sources and installed on
our server. I wanted to try out the feature of triggers in
postgres. Hence I took one example from the manual. Here it
is:

---------------------------------------------------------------------
drop function emp_stamp();
drop trigger emp_stamp on emp;
drop table emp;

create table emp (
        empname         text,
        salary          int4,
        last_date       datetime,
        last_user       name);

CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS '
BEGIN
           -- Check that empname and salary are given
           IF new.empname ISNULL THEN
                   RAISE EXCEPTION ''empname cannot be NULL value'';
           END IF;
           IF NEW.salary ISNULL THEN
                   RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
           END IF;

        -- Who works for us when she must pay for ?
           IF new.salary < 0 THEN
                   RAISE EXCEPTION ''% cannot have a negative salary'',new.empname;
        END IF;

    -- Remember who changed the payroll when
        NEW.last_date := ''now'';
        NEW.last_user := getpgusername();
        RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp FOR
EACH ROW EXECUTE PROCEDURE emp_stamp();
---------------------------------------------------------------------

I have added support of procedural language 'plpgsql' in
postgres system table pg_language. Now when I try to insert a record
in the emp table :

insert into emp(empname, salary) values( 'test', 1000 );

from psql I get the following error :

psql:rec.sql:1:ERROR:frmgr_info:function 0:cache lookup failed

Can anybody help me to find out the cause of this error ?

Thanks in advance.

Warm Regards

--
Rajesh Fowkar
V. S. DEMPO & CO. LTD., PANAJI-GOA

Email ID : Mail@dempos.com, rfowkar@yahoo.com
Web Site : http://www.dempos.com

Penguin <Mail@dempos.com> writes:
> I have added support of procedural language 'plpgsql' in
> postgres system table pg_language. Now when I try to insert a record
> in the emp table :

> insert into emp(empname, salary) values( 'test', 1000 );

> from psql I get the following error :

> psql:rec.sql:1:ERROR:frmgr_info:function 0:cache lookup failed

I copied-and-pasted your example, and it works fine for me on 7.0.*:

play=> insert into emp(empname, salary) values( 'test', 1000 );
INSERT 4576610 1
play=> insert into emp(empname, salary) values( 'test', -1);
ERROR:  test cannot have a negative salary

I suspect you messed up the installation of 'plpgsql' somehow.
Did you use the createlang script for that, or do it by hand?

            regards, tom lane