Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XC49X-0006Rb-By for pgsql-sql@arkaria.postgresql.org; Tue, 29 Jul 2014 09:57:03 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XC49W-0003ws-Sd for pgsql-sql@arkaria.postgresql.org; Tue, 29 Jul 2014 09:57:02 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XC49V-0003wi-S5 for pgsql-sql@postgresql.org; Tue, 29 Jul 2014 09:57:02 +0000 Received: from mail-pd0-x232.google.com ([2607:f8b0:400e:c02::232]) by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XC49S-0007Zv-KY for pgsql-sql@postgresql.org; Tue, 29 Jul 2014 09:57:00 +0000 Received: by mail-pd0-f178.google.com with SMTP id w10so11400569pde.37 for ; Tue, 29 Jul 2014 02:56:57 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; bh=xvguH+mHLQSSZpEUlJ0wLawgsryt6H0HDUNymEYZEVA=; b=IvyqO+JSNJme5Im022zW0RDWcDbgHxs+c1YeL8n+qgf0Y80jJeiFXfrfAtZpaKpjjq f5HKyv/cnOX9QCXuCrX/aGQ9aVCK5dVKSR8QZGfs7EBscdeP+qkoBOwnv9BCY0My/er+ PXlJ6bHlIvhzjKRPwCuMx4VyoZRxIu2AHj3Xb6DmdVk3yFqeKLxnmfZBKcp+3OGRs7+N pg9iDojWtHNZYB4zhQFAribnnDlTQdJaHa9w9vyMweG0XEJWq2Ie226iPO0ULzTBBcID IOOVAFSopxuSBq+ura+Vq7kD+dr+g0O6lty1MoJRoz2g3l+TkU7nIU55W8MCFmaMu2zQ 1BzA== X-Received: by 10.68.69.46 with SMTP id b14mr982482pbu.70.1406627817257; Tue, 29 Jul 2014 02:56:57 -0700 (PDT) MIME-Version: 1.0 Received: by 10.70.104.243 with HTTP; Tue, 29 Jul 2014 02:56:17 -0700 (PDT) In-Reply-To: References: From: Pavel Stehule Date: Tue, 29 Jul 2014 11:56:17 +0200 Message-ID: Subject: Re: Update columns in the same table in a deferred constraint trigger To: Andreas Joseph Krogh Cc: postgres list Content-Type: multipart/related; boundary=001a1138189461121104ff520f10 X-Pg-Spam-Score: 1.3 (+) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --001a1138189461121104ff520f10 Content-Type: multipart/alternative; boundary=001a1138189461120e04ff520f0f --001a1138189461120e04ff520f0f Content-Type: text/plain; charset=UTF-8 Hi 2014-07-29 11:52 GMT+02:00 Andreas Joseph Krogh : > 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; > NEW.autofisk = 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 Regards Pavel > 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 > www.visena.com > > --001a1138189461120e04ff520f0f Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi


2014-07-29 11:52 GMT+02:00 Andreas Joseph Krogh <andrea= s@visena.com>:
Hi all.
=C2=A0
I have this simple schema:
=C2=A0
create table fisk(
=C2=A0=C2=A0=C2=A0 name varchar primary key,
=C2=A0=C2=A0=C2=A0 autofisk varchar
);
=C2=A0
I want to update the column "autofisk" on commit based the v= alue of "name", so I created this trigger:
=C2=A0
CREATE OR REPLACE FUNCTION fisk_tf() returns TRIGGER AS $$
BEGIN
=C2=A0=C2=A0=C2=A0 raise notice 'name %', NEW.name;
=C2=A0=C2=A0=C2=A0 NEW.autofisk =3D NEW.name || CURRENT_TIMESTAMP::text;
=C2=A0=C2=A0=C2=A0 RETURN NEW;
END;
$$ LANGUAGE plpgsql;
=C2=A0
CREATE CONSTRAINT TRIGGER fisk_t AFTER INSERT OR UPDATE ON fisk DEFERR= ABLE INITIALLY DEFERRED

It should= be BEFORE INSERT OR UPDATE trigger

Regards

Pavel

=C2=A0
FOR EACH ROW EXECUTE PROCEDURE fisk_tf();
=C2=A0
The problem is that "autofisk" is never populated:
andreak=3D# begin;
BEGIN
andreak=3D# insert into fisk(name) values ('a');
INSERT 0 1
andreak=3D# commit;
NOTICE:=C2=A0 name a
COMMIT
andreak=3D# table fisk;
=C2=A0name | autofisk
------+----------
=C2=A0a=C2=A0=C2=A0=C2=A0 |
(1 row)
=C2=A0
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?
=C2=A0
Thanks.
=C2=A0
--
Andreas Jose= ph Krogh
CTO / Partner - Visena AS
3D""

--001a1138189461120e04ff520f0f-- --001a1138189461121104ff520f10 Content-Type: image/png Content-Disposition: inline Content-Transfer-Encoding: base64 Content-ID: X-Attachment-Id: 6c5fbe33aa5ecf91_0.1 iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgIfAhkiAAABzBJREFU aEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrAUgWRK/C6Al8H3lTgy0PGbzFdQc4VJP/H ADs43q6kROeJNbOYgQACIAgCWJKng4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/NPtk9uub7Gfd t5mBx1NhqSEo8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3saQaHt7fY70X949On aS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8WfCi80Xgf9vVlrhg9yRONe /f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl94bAniukEbo/n7YPmuTETzIab4Y9ZWCrKexd 8M58lxPCvvD6aihfvexbEQoPYO8NQRO0JnddGO6FJYaVsBde7cXj7KRk4LsqDxQzCYeGsJNgaXZe +JXkyGgWINq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O6x1IvtD40GWyJYYdkF0jIbYZlN16xygI gl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk79DEPdQ5u0g1vCvvqKXIW8mZYW+F3Tg4r8HvZ kQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMH QPwReg2wW8umEe/+L8T/LEzBGF9nXjzZ46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aY hdiCnRC7jaj7eiK2ETInC5PRF6LYkSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZTDUEj8u6IVhu EhRUJ2U2g6UlugyNb03Hl9obHwlxJbcRzcYjO4QPjVfGFTQav4vrmp7cpMp2qbHnBxWJbisbho2Q XI6C1sIHDUFhH4Hij4UbIev66eANeiwbkA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8U BIYhBd6oSUgYMijOkecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6Okjzx JO5afGe6kmhBicWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdSPAvxHYin6YmN9YBX QmTYZZNh14vHhhhal5vtcIrJjmvszPRJdExH3MWHvykoBEc9CoCGWJisOLOGoCORs1FvIMbYA8z3 kwM59oemy6LlWrLxFLmWgiQAfEGd8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNj v/gANOdQxPfciP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcPc4rV NjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5pqCuK01k40kSNtnKiKIl UfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/bdb9WRjmMk3jyUMAbIoyJq9e4cVmgzKt9j5gN J/aYDhk+hhjExwaPcz5PObA5Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCWqBQ9nCjJ5neivC82 Nr4LeS2j1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3TpGRpk0Tzpj3ZEJXi/GR a6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqpHLu9jBrhUuhNwVGbpyZrzrl0 HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1t/C/SU+cAGtdGo+fVnzycUX5wmn6eCKPmWYJG2E/ ppTsuRCbvUD9fwquksG5GqLVKhzDQ3GrEyLKSXhsiK3T5j9EyxffCFOYi2wUlPyFFDQAhehESDjQ GoX0ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnHqcas/72wvWF+mSf7N/Watp9kTXoluzeS0fB9 9CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9ITbScZWs7xOYNt9pwxSZtYDsX/GE3xTkrXgwAsXm fud08FiTeC+m2/o7ppo+PTS/NBK5ARpDG5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofs sxBA1gj5NXGvfJu/Qh7HwQh/VDUEyUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+ olK0A1EKRCE4snsh42w8Mv/Zhxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsYKyZxvxZSZBAgjhIz YDz+AbfD37GtbaoSKzgGt+lKfI/GZtay6vG4VXTpPsh+IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuu huC4pCGGx+YD2fcc5tfIAA0h/Et4/jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3 CHU2mTMwZ+B/y8DfSMBLLOYXVuEAAAAASUVORK5CYII= --001a1138189461121104ff520f10--