Date: Thu, 7 Nov 2019 17:28:18 -0500
From: Bruce Momjian <bruce@momjian.us>
To: Eugen Konkov <kes-kes@yandex.ru>
Cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org>,
	pgsql-docs@lists.postgresql.org
Subject: Re: Does 'instead of delete' trigger support modification of OLD
Message-ID: <20191107222818.GA18712@momjian.us>
References: <919823407.20191029175436@yandex.ru>
 <20191106185935.GE1843@momjian.us>
 <1771593631.20191107112032@yandex.ru>
 <352093181.20191107112429@yandex.ru>
 <20191107212655.GA487@momjian.us>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="oyUTqETQ0mS9luUI"
Content-Disposition: inline
In-Reply-To: <20191107212655.GA487@momjian.us>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk


--oyUTqETQ0mS9luUI
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Thu, Nov  7, 2019 at 04:26:55PM -0500, Bruce Momjian wrote:
> On Thu, Nov  7, 2019 at 11:24:29AM +0200, Eugen Konkov wrote:
> > >> As far as allowing DELETE to modify the trigger row for RETURNING, I am
> > >> not sure how much work it would take to allow that, but it seems like it
> > >> is a valid requite, and if so, I can add it to the TODO list.
> > 
> > > Yes,  Add please into TODO the feature to "allowing DELETE to modify the trigger row
> > > for  RETURNING".  Becuase, as I have described at first letter, without
> > > this the RETURNING rows **does not correspond actually deleted data**
> > 
> > > Thank you.
> 
> I have added a TODO item:
> 
> 	Allow DELETE triggers to modify rows, for use by RETURNING 

Thinking some more on this, I now don't think a TODO makes sense, so I
have removed it.

Triggers are designed to check and modify input data, and since DELETE
has no input data, it makes no sense.  In the attached SQL script, you
can see that only the BEFORE INSERT trigger fires, so there is no way
even with INSERT to change what is passed after the write to RETURNING. 
What you can do is to modify the returning expression, which is what I
have done for the last query --- hopefully that will help you.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

--oyUTqETQ0mS9luUI
Content-Type: application/x-sql
Content-Disposition: attachment; filename="trigtest.sql"
Content-Transfer-Encoding: quoted-printable

DROP TABLE IF EXISTS trigtest;=0A=0ACREATE TABLE trigtest(x INTEGER);=0A=0A=
CREATE OR REPLACE FUNCTION trigtest_new_func1()=0ARETURNS TRIGGER AS $$=0AB=
EGIN=0A	NEW.x =3D NEW.x + 10;=0A	RETURN NEW;=0AEND;=0A$$ LANGUAGE plpgsql;=
=0A=0ACREATE OR REPLACE FUNCTION trigtest_new_func2()=0ARETURNS TRIGGER AS =
$$=0ABEGIN=0A	NEW.x =3D NEW.x + 100;=0A	RETURN NEW;=0AEND;=0A$$ LANGUAGE pl=
pgsql;=0A=0ACREATE TRIGGER trigtest_trigger1=0ABEFORE INSERT ON trigtest=0A=
FOR EACH ROW EXECUTE PROCEDURE trigtest_new_func1();=0A=0ACREATE TRIGGER tr=
igtest_trigger2=0AAFTER INSERT ON trigtest=0AFOR EACH ROW EXECUTE PROCEDURE=
 trigtest_new_func2();=0A=0ACREATE OR REPLACE FUNCTION trigtest_old_func1()=
=0ARETURNS TRIGGER AS $$=0ABEGIN=0A	OLD.x =3D OLD.x + 1000;=0A	RETURN OLD;=
=0AEND;=0A$$ LANGUAGE plpgsql;=0A=0ACREATE OR REPLACE FUNCTION trigtest_old=
_func2()=0ARETURNS TRIGGER AS $$=0ABEGIN=0A	OLD.x =3D OLD.x + 10000;=0A	RET=
URN OLD;=0AEND;=0A$$ LANGUAGE plpgsql;=0A=0ACREATE TRIGGER trigtest_trigger=
3=0ABEFORE DELETE ON trigtest=0AFOR EACH ROW EXECUTE PROCEDURE trigtest_old=
_func1();=0A=0ACREATE TRIGGER trigtest_trigger4=0AAFTER DELETE ON trigtest=
=0AFOR EACH ROW EXECUTE PROCEDURE trigtest_old_func2();=0A=0AINSERT INTO tr=
igtest VALUES (1) RETURNING x;=0A=0ASELECT * FROM trigtest;=0A=0ADELETE FRO=
M trigtest RETURNING x + 1000000;=0A
--oyUTqETQ0mS9luUI--