I am using postgresql version 7.2.3, and have the following situation.
When I attempt to add a function, I get the error CreateTrigger: function
mem_leveled() does not exist. Using the function in psql (i.e. SELECT
mem_leveled('fubar', 4, '2002/12/30 10:09:00 GMT'); ) works fine, I just
can't create a trigger for it.
I know its something stupid I'm doing (or not doing).
CREATE TABLE members ( name CHARACTER VARYING(256), level smallint, date_updated timestamptz, ... other stuff
...
)
CREATE TABLE mem_history ( name CHARACTER VARYING(256), level smallint, date_achieved timestamptz
)
CREATE FUNCTION mem_leveled (varchar, smallint, timestamptz) RETURN BOOLEAN
AS
'DELCARE mem_lvl RECORD;
BEGIN SELECT INTO mem_lvl * FROM mem_history WHERE name = $1 AND level = $2; IF NOT FOUND THEN INSERT INTO
mem_historyVALUES ($1, $2, $3); END IF; RETURN TRUE;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER update_mem_level AFTER INSERT OR UPDATE
ON members FOR EACH ROW
EXECUTE PROCEDURE mem_leveled('name', 'level', 'date_updated');
------
Dave A.
