| From: | "Sigi Jekabsons" <sigi(dot)j(at)workskillsprofessionals(dot)com(dot)au> | 
|---|---|
| To: | <pgsql-sql(at)postgresql(dot)org> | 
| Subject: | trouble creating trigger | 
| Date: | 2003-04-22 02:28:24 | 
| Message-ID: | 006e01c30876$d9ddf0f0$0302a8c0@microvac | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-sql | 
I can create the function the trigger is calling, and I can use the function
in a SELECT, and it all works fine, but postgres won't let me create a
trigger using that function.
Here I'm creating the function:
asp_employ=#  CREATE FUNCTION upd_act_date(int4) RETURNS INTEGER AS '
asp_employ'# DECLARE
asp_employ'#     cand_id_arg ALIAS FOR $1;
asp_employ'# BEGIN
asp_employ'#     UPDATE cands SET actlog_updated_last = ( SELECT
MAX(date_logged) FROM cand_log WHERE cand_id = cand_id_arg ) WHERE cand_id =
cand_id_arg;
asp_employ'#     RETURN 1;
asp_employ'# END;
asp_employ'# ' LANGUAGE 'plpgsql';
CREATE FUNCTION
And the function appears to work okay:
 asp_employ=# SELECT upd_act_date('003234');
 upd_act_date
--------------
            1
(1 row)
However I can't create the trigger:
asp_employ=# CREATE TRIGGER upd_act_date_tg
asp_employ-#     BEFORE INSERT OR UPDATE ON cand_log FOR EACH ROW
asp_employ-#     EXECUTE PROCEDURE upd_act_date(cand_id);
ERROR:  CreateTrigger: function upd_act_date() does not exist
This doesn't work either:
asp_employ=#  CREATE TRIGGER upd_act_date_tg
asp_employ-#     AFTER INSERT OR UPDATE ON cand_log
asp_employ-#     FOR EACH ROW EXECUTE PROCEDURE upd_act_date('003234');
ERROR:  CreateTrigger: function upd_act_date() does not exist
What am I doing wrong?
This is on Postgres 7.3.2 on debian.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Stephan Szabo | 2003-04-22 03:58:57 | Re: trouble creating trigger | 
| Previous Message | Mario Alberto Soto Cordones | 2003-04-21 21:58:38 | Re: PROBLEM WITH FUNCTIONS |