Thread: PLPGSQL problem
Hi there,
I am not sure whether this is the right mail.
I am having problem with PLPGSQL.
I have set up the langucage in pg_language table as mentioned in the docs.
I am able to create functions and triggers. But when I insert a record into the table It shows an error as shown below
ERROR: fmgr_info: function 0: cache lookup failed
Details about the table,function and trigger and the query I ran
drop table emp1;
CREATE TABLE emp1 (
empname text,
salary int4,
last_date datetime,
last_user name);
empname text,
salary int4,
last_date datetime,
last_user name);
drop function emp_stamp();
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?
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';
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';
drop trigger emp_stamp;
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp1 FOR EACH ROW
EXECUTE PROCEDURE emp_stamp();
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp1 FOR EACH ROW
EXECUTE PROCEDURE emp_stamp();
gs=# insert into emp1 values('emp',43,now(),'');
Regards,
Srini