pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcatch an 'update where false' ?
6+ messages / 4 participants
[nested] [flat]
* catch an 'update where false' ?
@ 2005-08-12 15:13 santiago <chetzacoalt@free.fr>
0 siblings, 1 reply; 6+ messages in thread
From: santiago @ 2005-08-12 15:13 UTC (permalink / raw)
To: pgsql-sql
hello
I' trying to catch un update that fails to update because no row matchs
the where clause:
for instance:
update t set c=1 where cc=2;
--and no row with cc=2
I would like to perform a
insert into t (c) values 1;
before the update is resolved
I hoped a trigger before update could help me, but it seems
that if where clause evals to false, triggers are not called..
someone knows a way around this ?
thanks
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: catch an 'update where false' ?
@ 2005-08-12 15:27 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: santiago <chetzacoalt@free.fr>
0 siblings, 0 replies; 6+ messages in thread
From: Alvaro Herrera @ 2005-08-12 15:27 UTC (permalink / raw)
To: santiago <chetzacoalt@free.fr>; +Cc: pgsql-sql
On Fri, Aug 12, 2005 at 05:13:24PM +0200, santiago wrote:
> hello
>
> I' trying to catch un update that fails to update because no row matchs
> the where clause:
>
> for instance:
> update t set c=1 where cc=2;
> --and no row with cc=2
>
> I would like to perform a
> insert into t (c) values 1;
> before the update is resolved
>
> I hoped a trigger before update could help me, but it seems
> that if where clause evals to false, triggers are not called..
Triggers FOR EACH ROW are called once for each updated row. I think you
could try with a "FOR EACH STATEMENT" trigger.
--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"There is evil in the world. There are dark, awful things. Occasionally, we get
a glimpse of them. But there are dark corners; horrors almost impossible to
imagine... even in our worst nightmares." (Van Helsing, Dracula A.D. 1972)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: catch an 'update where false' ?
@ 2005-08-14 13:13 santiago <chetzacoalt@free.fr>
0 siblings, 3 replies; 6+ messages in thread
From: santiago @ 2005-08-14 13:13 UTC (permalink / raw)
To: pgsql-sql
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.
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: catch an 'update where false' ?
@ 2005-08-15 03:22 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: santiago <chetzacoalt@free.fr>
2 siblings, 0 replies; 6+ messages in thread
From: Alvaro Herrera @ 2005-08-15 03:22 UTC (permalink / raw)
To: santiago <chetzacoalt@free.fr>; +Cc: pgsql-sql
On Sun, Aug 14, 2005 at 03:13:15PM +0200, santiago wrote:
> 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 ?
Yes, it was added in release 7.4.
--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
<Schwern> It does it in a really, really complicated way
<crab> why does it need to be complicated?
<Schwern> Because it's MakeMaker.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: catch an 'update where false' ?
@ 2005-08-15 08:24 Richard Huxton <dev@archonet.com>
parent: santiago <chetzacoalt@free.fr>
2 siblings, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2005-08-15 08:24 UTC (permalink / raw)
To: chetzacoalt@free.fr; +Cc: pgsql-sql
santiago wrote:
> 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.
Of course the other option is to make sure there is always a row
available to update. Impossible to say whether this is sensible without
knowing the precise details of your problem.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: catch an 'update where false' ?
@ 2005-08-15 15:00 Lane Van Ingen <lvaningen@esncc.com>
parent: santiago <chetzacoalt@free.fr>
2 siblings, 0 replies; 6+ messages in thread
From: Lane Van Ingen @ 2005-08-15 15:00 UTC (permalink / raw)
To: chetzacoalt@free.fr; 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 found then
-- 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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-08-15 15:00 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-12 15:13 catch an 'update where false' ? santiago <chetzacoalt@free.fr>
2005-08-12 15:27 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2005-08-14 13:13 Re: catch an 'update where false' ? santiago <chetzacoalt@free.fr>
2005-08-15 03:22 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2005-08-15 08:24 ` Richard Huxton <dev@archonet.com>
2005-08-15 15:00 ` Lane Van Ingen <lvaningen@esncc.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox