Обсуждение: 43.10 Trigger Functions one sentences kind of not easy to understand.
Row-level triggers firedBEFORE
can return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and theINSERT
/UPDATE
/DELETE
does not occur for this row). If a nonnull value is returned then the operation proceeds with that row value. Returning a row value different from the original value ofNEW
alters the row that will be inserted or updated. Thus, if the trigger function wants the triggering action to succeed normally without altering the row value,NEW
(or a value equal thereto) has to be returned. To alter the row to be stored, it is possible to replace single values directly inNEW
and return the modifiedNEW
, or to build a complete new record/row to return. In the case of a before-trigger onDELETE
, the returned value has no direct effect, but it has to be nonnull to allow the trigger action to proceed. Note thatNEW
is null inDELETE
triggers, so returning that is usually not sensible. The usual idiom inDELETE
triggers is to returnOLD
.
I just want confirm the highlighted sentence is equivalent as the following sql code:
begin;
CREATE TABLE documents (
docdesc text,
misc text,
modification_time timestamp with time zone DEFAULT now()
);
CREATE FUNCTION documents_update_mod_time() RETURNS trigger
AS $$
begin
new.modification_time := now();
return new;
end
$$
LANGUAGE plpgsql;
CREATE TRIGGER documents_modification_time
BEFORE INSERT OR UPDATE ON documents
FOR EACH ROW
EXECUTE PROCEDURE documents_update_mod_time();
commit;
as a non-native english speaker, I kind of feel this sentence quite hard to understand....
On 2022-Apr-27, Jian He wrote: > https://www.postgresql.org/docs/current/plpgsql-trigger.html > > > *Returning a row value different from the original value > > of NEW alters the row that will be inserted or updated.* > I just want confirm the highlighted sentence is equivalent as the > following sql code: Yes: > begin; > > CREATE TABLE documents ( > > docdesc text, > > misc text, > > modification_time timestamp with time zone DEFAULT now() > > ); > > CREATE FUNCTION documents_update_mod_time() RETURNS trigger > > AS $$ > > begin > > new.modification_time := now(); > > > return new; > > > end > > $$ > > LANGUAGE plpgsql; > > CREATE TRIGGER documents_modification_time > > BEFORE INSERT OR UPDATE ON documents > > FOR EACH ROW > > EXECUTE PROCEDURE documents_update_mod_time(); > > commit; This is the usual way to modify the row being inserted. > As a non-native english speaker, I kind of feel this sentence quite hard to > understand.... Maybe you can split it up at periods and think about individual parts separately. The docs are intentionally packed very densely with information, to avoid making them longer than they already are. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/