aboutsummaryrefslogtreecommitdiff
path: root/contrib/postgres_fdw
diff options
context:
space:
mode:
Diffstat (limited to 'contrib/postgres_fdw')
-rw-r--r--contrib/postgres_fdw/deparse.c63
-rw-r--r--contrib/postgres_fdw/expected/postgres_fdw.out319
-rw-r--r--contrib/postgres_fdw/postgres_fdw.c4
-rw-r--r--contrib/postgres_fdw/sql/postgres_fdw.sql216
4 files changed, 574 insertions, 28 deletions
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 2dfe80da0af..32c01350714 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -110,6 +110,7 @@ static void deparseTargetList(StringInfo buf,
List **retrieved_attrs);
static void deparseReturningList(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
+ bool trig_after_row,
List *returningList,
List **retrieved_attrs);
static void deparseColumnRef(StringInfo buf, int varno, int varattno,
@@ -875,11 +876,9 @@ deparseInsertSql(StringInfo buf, PlannerInfo *root,
else
appendStringInfoString(buf, " DEFAULT VALUES");
- if (returningList)
- deparseReturningList(buf, root, rtindex, rel, returningList,
- retrieved_attrs);
- else
- *retrieved_attrs = NIL;
+ deparseReturningList(buf, root, rtindex, rel,
+ rel->trigdesc && rel->trigdesc->trig_insert_after_row,
+ returningList, retrieved_attrs);
}
/*
@@ -919,11 +918,9 @@ deparseUpdateSql(StringInfo buf, PlannerInfo *root,
}
appendStringInfoString(buf, " WHERE ctid = $1");
- if (returningList)
- deparseReturningList(buf, root, rtindex, rel, returningList,
- retrieved_attrs);
- else
- *retrieved_attrs = NIL;
+ deparseReturningList(buf, root, rtindex, rel,
+ rel->trigdesc && rel->trigdesc->trig_update_after_row,
+ returningList, retrieved_attrs);
}
/*
@@ -943,34 +940,48 @@ deparseDeleteSql(StringInfo buf, PlannerInfo *root,
deparseRelation(buf, rel);
appendStringInfoString(buf, " WHERE ctid = $1");
- if (returningList)
- deparseReturningList(buf, root, rtindex, rel, returningList,
- retrieved_attrs);
- else
- *retrieved_attrs = NIL;
+ deparseReturningList(buf, root, rtindex, rel,
+ rel->trigdesc && rel->trigdesc->trig_delete_after_row,
+ returningList, retrieved_attrs);
}
/*
- * deparse RETURNING clause of INSERT/UPDATE/DELETE
+ * Add a RETURNING clause, if needed, to an INSERT/UPDATE/DELETE.
*/
static void
deparseReturningList(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
+ bool trig_after_row,
List *returningList,
List **retrieved_attrs)
{
- Bitmapset *attrs_used;
+ Bitmapset *attrs_used = NULL;
- /*
- * We need the attrs mentioned in the query's RETURNING list.
- */
- attrs_used = NULL;
- pull_varattnos((Node *) returningList, rtindex,
- &attrs_used);
+ if (trig_after_row)
+ {
+ /* whole-row reference acquires all non-system columns */
+ attrs_used =
+ bms_make_singleton(0 - FirstLowInvalidHeapAttributeNumber);
+ }
- appendStringInfoString(buf, " RETURNING ");
- deparseTargetList(buf, root, rtindex, rel, attrs_used,
- retrieved_attrs);
+ if (returningList != NIL)
+ {
+ /*
+ * We need the attrs, non-system and system, mentioned in the local
+ * query's RETURNING list.
+ */
+ pull_varattnos((Node *) returningList, rtindex,
+ &attrs_used);
+ }
+
+ if (attrs_used != NULL)
+ {
+ appendStringInfoString(buf, " RETURNING ");
+ deparseTargetList(buf, root, rtindex, rel, attrs_used,
+ retrieved_attrs);
+ }
+ else
+ *retrieved_attrs = NIL;
}
/*
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 9a3d6516672..671c329c920 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2496,3 +2496,322 @@ select * from rem1;
11 | bye remote
(4 rows)
+-- ===================================================================
+-- test local triggers
+-- ===================================================================
+-- Trigger functions "borrowed" from triggers regress test.
+CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS $$
+BEGIN
+ RAISE NOTICE 'trigger_func(%) called: action = %, when = %, level = %',
+ TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
+ RETURN NULL;
+END;$$;
+CREATE TRIGGER trig_stmt_before BEFORE DELETE OR INSERT OR UPDATE ON rem1
+ FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
+CREATE TRIGGER trig_stmt_after AFTER DELETE OR INSERT OR UPDATE ON rem1
+ FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
+CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger
+LANGUAGE plpgsql AS $$
+
+declare
+ oldnew text[];
+ relid text;
+ argstr text;
+begin
+
+ relid := TG_relid::regclass;
+ argstr := '';
+ for i in 0 .. TG_nargs - 1 loop
+ if i > 0 then
+ argstr := argstr || ', ';
+ end if;
+ argstr := argstr || TG_argv[i];
+ end loop;
+
+ RAISE NOTICE '%(%) % % % ON %',
+ tg_name, argstr, TG_when, TG_level, TG_OP, relid;
+ oldnew := '{}'::text[];
+ if TG_OP != 'INSERT' then
+ oldnew := array_append(oldnew, format('OLD: %s', OLD));
+ end if;
+
+ if TG_OP != 'DELETE' then
+ oldnew := array_append(oldnew, format('NEW: %s', NEW));
+ end if;
+
+ RAISE NOTICE '%', array_to_string(oldnew, ',');
+
+ if TG_OP = 'DELETE' then
+ return OLD;
+ else
+ return NEW;
+ end if;
+end;
+$$;
+-- Test basic functionality
+CREATE TRIGGER trig_row_before
+BEFORE INSERT OR UPDATE OR DELETE ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+CREATE TRIGGER trig_row_after
+AFTER INSERT OR UPDATE OR DELETE ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+delete from rem1;
+NOTICE: trigger_func(<NULL>) called: action = DELETE, when = BEFORE, level = STATEMENT
+NOTICE: trig_row_before(23, skidoo) BEFORE ROW DELETE ON rem1
+NOTICE: OLD: (1,hi)
+NOTICE: trig_row_before(23, skidoo) BEFORE ROW DELETE ON rem1
+NOTICE: OLD: (10,"hi remote")
+NOTICE: trig_row_before(23, skidoo) BEFORE ROW DELETE ON rem1
+NOTICE: OLD: (2,bye)
+NOTICE: trig_row_before(23, skidoo) BEFORE ROW DELETE ON rem1
+NOTICE: OLD: (11,"bye remote")
+NOTICE: trig_row_after(23, skidoo) AFTER ROW DELETE ON rem1
+NOTICE: OLD: (1,hi)
+NOTICE: trig_row_after(23, skidoo) AFTER ROW DELETE ON rem1
+NOTICE: OLD: (10,"hi remote")
+NOTICE: trig_row_after(23, skidoo) AFTER ROW DELETE ON rem1
+NOTICE: OLD: (2,bye)
+NOTICE: trig_row_after(23, skidoo) AFTER ROW DELETE ON rem1
+NOTICE: OLD: (11,"bye remote")
+NOTICE: trigger_func(<NULL>) called: action = DELETE, when = AFTER, level = STATEMENT
+insert into rem1 values(1,'insert');
+NOTICE: trigger_func(<NULL>) called: action = INSERT, when = BEFORE, level = STATEMENT
+NOTICE: trig_row_before(23, skidoo) BEFORE ROW INSERT ON rem1
+NOTICE: NEW: (1,insert)
+NOTICE: trig_row_after(23, skidoo) AFTER ROW INSERT ON rem1
+NOTICE: NEW: (1,insert)
+NOTICE: trigger_func(<NULL>) called: action = INSERT, when = AFTER, level = STATEMENT
+update rem1 set f2 = 'update' where f1 = 1;
+NOTICE: trigger_func(<NULL>) called: action = UPDATE, when = BEFORE, level = STATEMENT
+NOTICE: trig_row_before(23, skidoo) BEFORE ROW UPDATE ON rem1
+NOTICE: OLD: (1,insert),NEW: (1,update)
+NOTICE: trig_row_after(23, skidoo) AFTER ROW UPDATE ON rem1
+NOTICE: OLD: (1,insert),NEW: (1,update)
+NOTICE: trigger_func(<NULL>) called: action = UPDATE, when = AFTER, level = STATEMENT
+update rem1 set f2 = f2 || f2;
+NOTICE: trigger_func(<NULL>) called: action = UPDATE, when = BEFORE, level = STATEMENT
+NOTICE: trig_row_before(23, skidoo) BEFORE ROW UPDATE ON rem1
+NOTICE: OLD: (1,update),NEW: (1,updateupdate)
+NOTICE: trig_row_after(23, skidoo) AFTER ROW UPDATE ON rem1
+NOTICE: OLD: (1,update),NEW: (1,updateupdate)
+NOTICE: trigger_func(<NULL>) called: action = UPDATE, when = AFTER, level = STATEMENT
+-- cleanup
+DROP TRIGGER trig_row_before ON rem1;
+DROP TRIGGER trig_row_after ON rem1;
+DROP TRIGGER trig_stmt_before ON rem1;
+DROP TRIGGER trig_stmt_after ON rem1;
+DELETE from rem1;
+-- Test WHEN conditions
+CREATE TRIGGER trig_row_before_insupd
+BEFORE INSERT OR UPDATE ON rem1
+FOR EACH ROW
+WHEN (NEW.f2 like '%update%')
+EXECUTE PROCEDURE trigger_data(23,'skidoo');
+CREATE TRIGGER trig_row_after_insupd
+AFTER INSERT OR UPDATE ON rem1
+FOR EACH ROW
+WHEN (NEW.f2 like '%update%')
+EXECUTE PROCEDURE trigger_data(23,'skidoo');
+-- Insert or update not matching: nothing happens
+INSERT INTO rem1 values(1, 'insert');
+UPDATE rem1 set f2 = 'test';
+-- Insert or update matching: triggers are fired
+INSERT INTO rem1 values(2, 'update');
+NOTICE: trig_row_before_insupd(23, skidoo) BEFORE ROW INSERT ON rem1
+NOTICE: NEW: (2,update)
+NOTICE: trig_row_after_insupd(23, skidoo) AFTER ROW INSERT ON rem1
+NOTICE: NEW: (2,update)
+UPDATE rem1 set f2 = 'update update' where f1 = '2';
+NOTICE: trig_row_before_insupd(23, skidoo) BEFORE ROW UPDATE ON rem1
+NOTICE: OLD: (2,update),NEW: (2,"update update")
+NOTICE: trig_row_after_insupd(23, skidoo) AFTER ROW UPDATE ON rem1
+NOTICE: OLD: (2,update),NEW: (2,"update update")
+CREATE TRIGGER trig_row_before_delete
+BEFORE DELETE ON rem1
+FOR EACH ROW
+WHEN (OLD.f2 like '%update%')
+EXECUTE PROCEDURE trigger_data(23,'skidoo');
+CREATE TRIGGER trig_row_after_delete
+AFTER DELETE ON rem1
+FOR EACH ROW
+WHEN (OLD.f2 like '%update%')
+EXECUTE PROCEDURE trigger_data(23,'skidoo');
+-- Trigger is fired for f1=2, not for f1=1
+DELETE FROM rem1;
+NOTICE: trig_row_before_delete(23, skidoo) BEFORE ROW DELETE ON rem1
+NOTICE: OLD: (2,"update update")
+NOTICE: trig_row_after_delete(23, skidoo) AFTER ROW DELETE ON rem1
+NOTICE: OLD: (2,"update update")
+-- cleanup
+DROP TRIGGER trig_row_before_insupd ON rem1;
+DROP TRIGGER trig_row_after_insupd ON rem1;
+DROP TRIGGER trig_row_before_delete ON rem1;
+DROP TRIGGER trig_row_after_delete ON rem1;
+-- Test various RETURN statements in BEFORE triggers.
+CREATE FUNCTION trig_row_before_insupdate() RETURNS TRIGGER AS $$
+ BEGIN
+ NEW.f2 := NEW.f2 || ' triggered !';
+ RETURN NEW;
+ END
+$$ language plpgsql;
+CREATE TRIGGER trig_row_before_insupd
+BEFORE INSERT OR UPDATE ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate();
+-- The new values should have 'triggered' appended
+INSERT INTO rem1 values(1, 'insert');
+SELECT * from loc1;
+ f1 | f2
+----+--------------------
+ 1 | insert triggered !
+(1 row)
+
+INSERT INTO rem1 values(2, 'insert') RETURNING f2;
+ f2
+--------------------
+ insert triggered !
+(1 row)
+
+SELECT * from loc1;
+ f1 | f2
+----+--------------------
+ 1 | insert triggered !
+ 2 | insert triggered !
+(2 rows)
+
+UPDATE rem1 set f2 = '';
+SELECT * from loc1;
+ f1 | f2
+----+--------------
+ 1 | triggered !
+ 2 | triggered !
+(2 rows)
+
+UPDATE rem1 set f2 = 'skidoo' RETURNING f2;
+ f2
+--------------------
+ skidoo triggered !
+ skidoo triggered !
+(2 rows)
+
+SELECT * from loc1;
+ f1 | f2
+----+--------------------
+ 1 | skidoo triggered !
+ 2 | skidoo triggered !
+(2 rows)
+
+DELETE FROM rem1;
+-- Add a second trigger, to check that the changes are propagated correctly
+-- from trigger to trigger
+CREATE TRIGGER trig_row_before_insupd2
+BEFORE INSERT OR UPDATE ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate();
+INSERT INTO rem1 values(1, 'insert');
+SELECT * from loc1;
+ f1 | f2
+----+--------------------------------
+ 1 | insert triggered ! triggered !
+(1 row)
+
+INSERT INTO rem1 values(2, 'insert') RETURNING f2;
+ f2
+--------------------------------
+ insert triggered ! triggered !
+(1 row)
+
+SELECT * from loc1;
+ f1 | f2
+----+--------------------------------
+ 1 | insert triggered ! triggered !
+ 2 | insert triggered ! triggered !
+(2 rows)
+
+UPDATE rem1 set f2 = '';
+SELECT * from loc1;
+ f1 | f2
+----+--------------------------
+ 1 | triggered ! triggered !
+ 2 | triggered ! triggered !
+(2 rows)
+
+UPDATE rem1 set f2 = 'skidoo' RETURNING f2;
+ f2
+--------------------------------
+ skidoo triggered ! triggered !
+ skidoo triggered ! triggered !
+(2 rows)
+
+SELECT * from loc1;
+ f1 | f2
+----+--------------------------------
+ 1 | skidoo triggered ! triggered !
+ 2 | skidoo triggered ! triggered !
+(2 rows)
+
+DROP TRIGGER trig_row_before_insupd ON rem1;
+DROP TRIGGER trig_row_before_insupd2 ON rem1;
+DELETE from rem1;
+INSERT INTO rem1 VALUES (1, 'test');
+-- Test with a trigger returning NULL
+CREATE FUNCTION trig_null() RETURNS TRIGGER AS $$
+ BEGIN
+ RETURN NULL;
+ END
+$$ language plpgsql;
+CREATE TRIGGER trig_null
+BEFORE INSERT OR UPDATE OR DELETE ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trig_null();
+-- Nothing should have changed.
+INSERT INTO rem1 VALUES (2, 'test2');
+SELECT * from loc1;
+ f1 | f2
+----+------
+ 1 | test
+(1 row)
+
+UPDATE rem1 SET f2 = 'test2';
+SELECT * from loc1;
+ f1 | f2
+----+------
+ 1 | test
+(1 row)
+
+DELETE from rem1;
+SELECT * from loc1;
+ f1 | f2
+----+------
+ 1 | test
+(1 row)
+
+DROP TRIGGER trig_null ON rem1;
+DELETE from rem1;
+-- Test a combination of local and remote triggers
+CREATE TRIGGER trig_row_before
+BEFORE INSERT OR UPDATE OR DELETE ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+CREATE TRIGGER trig_row_after
+AFTER INSERT OR UPDATE OR DELETE ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+CREATE TRIGGER trig_local_before BEFORE INSERT OR UPDATE ON loc1
+FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate();
+INSERT INTO rem1(f2) VALUES ('test');
+NOTICE: trig_row_before(23, skidoo) BEFORE ROW INSERT ON rem1
+NOTICE: NEW: (12,test)
+NOTICE: trig_row_after(23, skidoo) AFTER ROW INSERT ON rem1
+NOTICE: NEW: (12,"test triggered !")
+UPDATE rem1 SET f2 = 'testo';
+NOTICE: trig_row_before(23, skidoo) BEFORE ROW UPDATE ON rem1
+NOTICE: OLD: (12,"test triggered !"),NEW: (12,testo)
+NOTICE: trig_row_after(23, skidoo) AFTER ROW UPDATE ON rem1
+NOTICE: OLD: (12,"test triggered !"),NEW: (12,"testo triggered !")
+-- Test returning system attributes
+INSERT INTO rem1(f2) VALUES ('test') RETURNING ctid, xmin, xmax;
+NOTICE: trig_row_before(23, skidoo) BEFORE ROW INSERT ON rem1
+NOTICE: NEW: (13,test)
+NOTICE: trig_row_after(23, skidoo) AFTER ROW INSERT ON rem1
+NOTICE: NEW: (13,"test triggered !")
+ ctid | xmin | xmax
+--------+------+------------
+ (0,27) | 180 | 4294967295
+(1 row)
+
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 1ae1c184372..d7c5fa21195 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -108,7 +108,7 @@ enum FdwScanPrivateIndex
* 1) INSERT/UPDATE/DELETE statement text to be sent to the remote server
* 2) Integer list of target attribute numbers for INSERT/UPDATE
* (NIL for a DELETE)
- * 3) Boolean flag showing if there's a RETURNING clause
+ * 3) Boolean flag showing if the remote query has a RETURNING clause
* 4) Integer list of attribute numbers retrieved by RETURNING, if any
*/
enum FdwModifyPrivateIndex
@@ -1246,7 +1246,7 @@ postgresPlanForeignModify(PlannerInfo *root,
*/
return list_make4(makeString(sql.data),
targetAttrs,
- makeInteger((returningList != NIL)),
+ makeInteger((retrieved_attrs != NIL)),
retrieved_attrs);
}
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 21b15ca9ff2..d47ceca3c11 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -390,3 +390,219 @@ insert into loc1(f2) values('bye');
insert into rem1(f2) values('bye remote');
select * from loc1;
select * from rem1;
+
+-- ===================================================================
+-- test local triggers
+-- ===================================================================
+
+-- Trigger functions "borrowed" from triggers regress test.
+CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS $$
+BEGIN
+ RAISE NOTICE 'trigger_func(%) called: action = %, when = %, level = %',
+ TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
+ RETURN NULL;
+END;$$;
+
+CREATE TRIGGER trig_stmt_before BEFORE DELETE OR INSERT OR UPDATE ON rem1
+ FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
+CREATE TRIGGER trig_stmt_after AFTER DELETE OR INSERT OR UPDATE ON rem1
+ FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
+
+CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger
+LANGUAGE plpgsql AS $$
+
+declare
+ oldnew text[];
+ relid text;
+ argstr text;
+begin
+
+ relid := TG_relid::regclass;
+ argstr := '';
+ for i in 0 .. TG_nargs - 1 loop
+ if i > 0 then
+ argstr := argstr || ', ';
+ end if;
+ argstr := argstr || TG_argv[i];
+ end loop;
+
+ RAISE NOTICE '%(%) % % % ON %',
+ tg_name, argstr, TG_when, TG_level, TG_OP, relid;
+ oldnew := '{}'::text[];
+ if TG_OP != 'INSERT' then
+ oldnew := array_append(oldnew, format('OLD: %s', OLD));
+ end if;
+
+ if TG_OP != 'DELETE' then
+ oldnew := array_append(oldnew, format('NEW: %s', NEW));
+ end if;
+
+ RAISE NOTICE '%', array_to_string(oldnew, ',');
+
+ if TG_OP = 'DELETE' then
+ return OLD;
+ else
+ return NEW;
+ end if;
+end;
+$$;
+
+-- Test basic functionality
+CREATE TRIGGER trig_row_before
+BEFORE INSERT OR UPDATE OR DELETE ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+
+CREATE TRIGGER trig_row_after
+AFTER INSERT OR UPDATE OR DELETE ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+
+delete from rem1;
+insert into rem1 values(1,'insert');
+update rem1 set f2 = 'update' where f1 = 1;
+update rem1 set f2 = f2 || f2;
+
+
+-- cleanup
+DROP TRIGGER trig_row_before ON rem1;
+DROP TRIGGER trig_row_after ON rem1;
+DROP TRIGGER trig_stmt_before ON rem1;
+DROP TRIGGER trig_stmt_after ON rem1;
+
+DELETE from rem1;
+
+
+-- Test WHEN conditions
+
+CREATE TRIGGER trig_row_before_insupd
+BEFORE INSERT OR UPDATE ON rem1
+FOR EACH ROW
+WHEN (NEW.f2 like '%update%')
+EXECUTE PROCEDURE trigger_data(23,'skidoo');
+
+CREATE TRIGGER trig_row_after_insupd
+AFTER INSERT OR UPDATE ON rem1
+FOR EACH ROW
+WHEN (NEW.f2 like '%update%')
+EXECUTE PROCEDURE trigger_data(23,'skidoo');
+
+-- Insert or update not matching: nothing happens
+INSERT INTO rem1 values(1, 'insert');
+UPDATE rem1 set f2 = 'test';
+
+-- Insert or update matching: triggers are fired
+INSERT INTO rem1 values(2, 'update');
+UPDATE rem1 set f2 = 'update update' where f1 = '2';
+
+CREATE TRIGGER trig_row_before_delete
+BEFORE DELETE ON rem1
+FOR EACH ROW
+WHEN (OLD.f2 like '%update%')
+EXECUTE PROCEDURE trigger_data(23,'skidoo');
+
+CREATE TRIGGER trig_row_after_delete
+AFTER DELETE ON rem1
+FOR EACH ROW
+WHEN (OLD.f2 like '%update%')
+EXECUTE PROCEDURE trigger_data(23,'skidoo');
+
+-- Trigger is fired for f1=2, not for f1=1
+DELETE FROM rem1;
+
+-- cleanup
+DROP TRIGGER trig_row_before_insupd ON rem1;
+DROP TRIGGER trig_row_after_insupd ON rem1;
+DROP TRIGGER trig_row_before_delete ON rem1;
+DROP TRIGGER trig_row_after_delete ON rem1;
+
+
+-- Test various RETURN statements in BEFORE triggers.
+
+CREATE FUNCTION trig_row_before_insupdate() RETURNS TRIGGER AS $$
+ BEGIN
+ NEW.f2 := NEW.f2 || ' triggered !';
+ RETURN NEW;
+ END
+$$ language plpgsql;
+
+CREATE TRIGGER trig_row_before_insupd
+BEFORE INSERT OR UPDATE ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate();
+
+-- The new values should have 'triggered' appended
+INSERT INTO rem1 values(1, 'insert');
+SELECT * from loc1;
+INSERT INTO rem1 values(2, 'insert') RETURNING f2;
+SELECT * from loc1;
+UPDATE rem1 set f2 = '';
+SELECT * from loc1;
+UPDATE rem1 set f2 = 'skidoo' RETURNING f2;
+SELECT * from loc1;
+
+DELETE FROM rem1;
+
+-- Add a second trigger, to check that the changes are propagated correctly
+-- from trigger to trigger
+CREATE TRIGGER trig_row_before_insupd2
+BEFORE INSERT OR UPDATE ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate();
+
+INSERT INTO rem1 values(1, 'insert');
+SELECT * from loc1;
+INSERT INTO rem1 values(2, 'insert') RETURNING f2;
+SELECT * from loc1;
+UPDATE rem1 set f2 = '';
+SELECT * from loc1;
+UPDATE rem1 set f2 = 'skidoo' RETURNING f2;
+SELECT * from loc1;
+
+DROP TRIGGER trig_row_before_insupd ON rem1;
+DROP TRIGGER trig_row_before_insupd2 ON rem1;
+
+DELETE from rem1;
+
+INSERT INTO rem1 VALUES (1, 'test');
+
+-- Test with a trigger returning NULL
+CREATE FUNCTION trig_null() RETURNS TRIGGER AS $$
+ BEGIN
+ RETURN NULL;
+ END
+$$ language plpgsql;
+
+CREATE TRIGGER trig_null
+BEFORE INSERT OR UPDATE OR DELETE ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trig_null();
+
+-- Nothing should have changed.
+INSERT INTO rem1 VALUES (2, 'test2');
+
+SELECT * from loc1;
+
+UPDATE rem1 SET f2 = 'test2';
+
+SELECT * from loc1;
+
+DELETE from rem1;
+
+SELECT * from loc1;
+
+DROP TRIGGER trig_null ON rem1;
+DELETE from rem1;
+
+-- Test a combination of local and remote triggers
+CREATE TRIGGER trig_row_before
+BEFORE INSERT OR UPDATE OR DELETE ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+
+CREATE TRIGGER trig_row_after
+AFTER INSERT OR UPDATE OR DELETE ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+
+CREATE TRIGGER trig_local_before BEFORE INSERT OR UPDATE ON loc1
+FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate();
+
+INSERT INTO rem1(f2) VALUES ('test');
+UPDATE rem1 SET f2 = 'testo';
+
+-- Test returning system attributes
+INSERT INTO rem1(f2) VALUES ('test') RETURNING ctid, xmin, xmax;
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-30 20:03:22 +0000