agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-sql@postgresql.org
Subject: Update columns in the same table in a deferred constraint trigger
Date: Tue, 29 Jul 2014 11:52:12 +0200 (CEST)
Message-ID: <VisenaEmail.39.362cb9f24c93a770.14781853e94@tc7-on> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
FOR EACH ROW EXECUTE PROCEDURE fisk_tf(); The problem is that "autofisk" is
never populated: andreak=# begin;
BEGIN
andreak=# insert into fisk(name) values ('a');
INSERT 0 1
andreak=# commit;
NOTICE: name a
COMMIT
andreak=# table fisk;
name | autofisk
------+----------
a |
(1 row) Is it possible to do what I want, namely to update a column in a
table in an AFTER INSERT OR UPDATE constraint trigger on the same table?
Thanks. -- 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.39.362cb9f24c93a770.14781853e94@tc7-on>
Permalink: ../VisenaEmail.39.362cb9f24c93a770.14781853e94@tc7-on/
Also on: postgresql.org/message-id/VisenaEmail.39.362cb9f24c93a770.14781853e94@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.39.362cb9f24c93a770.14781853e94@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