Re: catch an 'update where false' ? - Mailing list pgsql-sql
From | Lane Van Ingen |
---|---|
Subject | Re: catch an 'update where false' ? |
Date | |
Msg-id | EKEMKEFLOMKDDLIALABIIEFJCBAA.lvaningen@esncc.com Whole thread Raw |
In response to | Re: catch an 'update where false' ? (santiago <chetzacoalt@free.fr>) |
List | pgsql-sql |
santiago, here are the code snippets for you that come right out of a trigger that is being used in production today. This trigger is an after update trigger, so take that into consideration if needed. I think it will do for you what you need to do. CREATE OR REPLACE FUNCTION update_rpt_history() RETURNS "trigger" AS $BODY$ DECLARE rpt_hour_hist report_history%ROWTYPE; <snip> (other unrelated variables) <snip> work_hour_curr timestamp; work_hour_usage timestamp; work_report varchar; BEGIN <snip> (other unrelated logic) <snip> perform * from report_history where key_fld1 = work_hour_curr and key_fld2 = work_report; if not foundthen -- if there is no curr hr rcd for report, create it insert into report_history VALUES ( work_hour_curr, work_report, rpt_hour_hist.fld-1, rpt_hour_hist.fld-2, rpt_hour_hist.fld-3, rpt_hour_hist.fld-4, rpt_hour_hist.fld-5, rpt_hour_hist.fld-6); else -- if curr hr rcd for report, update it update report_history set ifID = rpt_hour_hist.ifID, fld-1= rpt_hour_hist.fld-1, fld-2 = rpt_hour_hist.fld-2, fld-3 = rpt_hour_hist.fld-3, fld-4 = rpt_hour_hist.fld-4, fld-5 = rpt_hour_hist.fld-5, fld-6 = rpt_hour_hist.fld-6 where key_fld1 = work_hour_curr and key_fld2 = work_report; -- neighbor end if; <snip> (other unrelated logic) <snip> RETURN NULL; END $BODY$ LANGUAGE 'plpgsql' STABLE; I am running PostgreSQL version 8.0 on Windows 2003 and Slackware Linux, if that makes a difference. -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of santiago Sent: Sunday, August 14, 2005 9:13 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] catch an 'update where false' ? Hi thanks all for your answers ! to Lane Van Ingen: my purpose is to write a trigger on update so that 'users' might then us update without having to perform the if found. I don't know if I was clear about that idea, but the checks on where update will or not succeed, is what I'm trying to hide from the upper-level users for this specific table. If what your sugesting works inside such a trigger, please show my some code of how to do that, beacause it's precisely what I fail to do. to Alvaro Herrera : according to my book, triggers are only implemented whit the FOR EACH ROW clause in postgres, and not available in the FOR EACH STATEMENT case. has this changed in some new version ? which shall I use then ? thanks to both ! Alvaro Herrera wrote: > On Fri, Aug 12, 2005 at 05:13:24PM +0200, santiago wrote: > > Triggers FOR EACH ROW are called once for each updated row. I think you > could try with a "FOR EACH STATEMENT" trigger. > Lane Van Ingen wrote: > Don't know where you are doing this, but I can think of a couple ways: > (1) Do a 'select count(*) <conditions> ....' on what you are trying to > update first to see if it returns a count greater than 0 > (2) If in pl/pgsql functions, you can use the 'IF FOUND' or 'IF NOT FOUND' > construct to see if update was successful > > NOTE: I saw that in version 8.1 you will be able to test a PostgreSQL- > supplied variable, but it is not available until 8.1 release. > ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org