agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-sql@postgresql.org
Subject: Re: Update columns in the same table in a deferred constraint trigger
Date: Tue, 29 Jul 2014 12:21:59 +0200 (CEST)
Message-ID: <VisenaEmail.3c.d75c646da6037c48.14781a1e4d7@tc7-on> (raw)
In-Reply-To: <CAFj8pRAKAv_KJCWOsMbm8690YYa_RS=APdy7wMcWDa1SVUBLYQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
På tirsdag 29. juli 2014 kl. 12:12:17, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: 2014-07-29 12:05
GMT+02:00 Andreas Joseph Krogh<andreas@visena.com <mailto:andreas@visena.com>>:
På tirsdag 29. juli 2014 kl. 12:01:48, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: 2014-07-29 11:59
GMT+02:00 Andreas Joseph Krogh<andreas@visena.com <mailto:andreas@visena.com>>:
På tirsdag 29. juli 2014 kl. 11:56:17, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: Hi 2014-07-29
11:52 GMT+02:00 Andreas Joseph Krogh<andreas@visena.com
<mailto:andreas@visena.com>>: Hi all. I have this simple schema: create
table fisk(
name varchar primary key,
autofisk varchar
); I want to update the column "autofisk" on commit based the value of
"name", so I created this trigger: CREATE OR REPLACE FUNCTION fisk_tf()
returns TRIGGER AS $$
BEGIN
raise notice 'name %', NEW.name <http://NEW.name;;
NEW.autofisk = NEW.name <http://NEW.name; || CURRENT_TIMESTAMP::text;
RETURN NEW;
END;
$$ LANGUAGE plpgsql; CREATE CONSTRAINT TRIGGER fisk_t AFTER INSERT OR
UPDATE ON fisk DEFERRABLE INITIALLY DEFERRED It should be BEFORE INSERT OR
UPDATE trigger He he, yes - I know that will work, but I need the trigger to
be run as a constraint-trigger, on commit, after all the data is populated in
other tables and this table. It is not possible - Postgres can change data
only before writing Is there a work-around, so I in the trigger can issue for
example: update fisk set autofisk = NEW.name <http://NEW.name; ||
CURRENT_TIMESTAMP::text where name = NEW.name; without it also triggering the
trigger? theoretically yes - you can disable triggers via ALTER TABLE DISABLE
TRIGGER
but then the code will be unmaintainable. Anything else is better than
dependency in triggers. You should to think about different solution.
Sometimes triggers can be replaced by functions directly called from
applications instead DML statements. I have tried this but the commit never
returns, I think because it recursively triggers the trigger again for that
modification. Will temporarily disabeling the trigger inside the trigger (in
a transaction) work? I really afraid of this strategy I see, so it boils
down to this being impossible at the moment. I really want this to be at the
DML-level so any modification done also updates the "autofisk"-column. Are
there any plans to make this work, that being modifying the same table in a
trigger running on it where the modification (comming form statements inside
the trigger-functino) like what I'm trying will not trigger the trigger? --
Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com> www.visena.com
<https://www.visena.com; <https://www.visena.com;
view thread (12+ messages) latest in thread
Message-ID: <VisenaEmail.3c.d75c646da6037c48.14781a1e4d7@tc7-on>
Permalink: ../VisenaEmail.3c.d75c646da6037c48.14781a1e4d7@tc7-on/
Also on: postgresql.org/message-id/VisenaEmail.3c.d75c646da6037c48.14781a1e4d7@tc7-on
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas@visena.com
Subject: Re: Update columns in the same table in a deferred constraint trigger
In-Reply-To: <VisenaEmail.3c.d75c646da6037c48.14781a1e4d7@tc7-on>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox