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:05:42 +0200 (CEST)
Message-ID: <VisenaEmail.3b.53bbc6fc6d6c2c43.1478192cae2@tc7-on> (raw)
In-Reply-To: <CAFj8pRCUtTwiMnokk6=V7g=wj2Pji-yCvUOxNQK_vVtYmnDSYg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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? 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?
--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.3b.53bbc6fc6d6c2c43.1478192cae2@tc7-on>
Permalink: ../VisenaEmail.3b.53bbc6fc6d6c2c43.1478192cae2@tc7-on/
Also on: postgresql.org/message-id/VisenaEmail.3b.53bbc6fc6d6c2c43.1478192cae2@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.3b.53bbc6fc6d6c2c43.1478192cae2@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