Include PostgreSQL in trigger test and correct documentation example

Include PostgreSQL dialect in trigger test and correct DDL example in documentation

A user highlighted that the syntax in the DDL trigger example was incorrect for PostgreSQL. The trigger tests where also skipping the PostgreSQL dialect until the syntax was corrected. This PR fixes both of these issues.

This pull request is:

- [X ] A documentation / typographical error fix
	- Good to go, no issue or tests are needed
- [ ] A short code fix
	- please include the issue number, and create an issue if none exists, which
	  must include a complete example of the issue.  one line code fixes without an
	  issue and demonstration will not be accepted.
	- Please include: `Fixes: #<issue number>` in the commit message
	- please include tests.   one line code fixes without tests will not be accepted.
- [ ] A new feature implementation
	- please include the issue number, and create an issue if none exists, which must
	  include a complete example of how the feature would look.
	- Please include: `Fixes: #<issue number>` in the commit message
	- please include tests.

**Have a nice day!**
Fixes: #4037

Closes: #5548
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/5548
Pull-request-sha: 1db5e47adb90f9d51e247711dcfdbb274fb7bf73

Change-Id: I06edbcab99c82a3ce25581b81f8d2a4a028c07c3

3 files changed, 62 insertions, 13 deletions

diff --git a/doc/build/core/ddl.rst b/doc/build/core/ddl.rst
index 30619a419..9c2fed198 100644
--- a/doc/build/core/ddl.rst
+++ b/doc/build/core/ddl.rst
@@ -59,9 +59,24 @@ the PostgreSQL backend, we could invoke this as::
         Column('data', String(50))
     )
 
+    func = DDL(
+        "CREATE FUNCTION my_func() "
+        "RETURNS TRIGGER AS $$ "
+        "BEGIN "
+        "NEW.data := 'ins'; "
+        "RETURN NEW; "
+        "END; $$ LANGUAGE PLPGSQL"
+    )
+
     trigger = DDL(
         "CREATE TRIGGER dt_ins BEFORE INSERT ON mytable "
-        "FOR EACH ROW BEGIN SET NEW.data='ins'; END"
+        "FOR EACH ROW EXECUTE PROCEDURE my_func();"
+    )
+
+    event.listen(
+        mytable,
+        'after_create',
+        func.execute_if(dialect='postgresql')
     )
 
     event.listen(
@@ -296,5 +311,3 @@ DDL Expression Constructs API
 .. autoclass:: DropSchema
     :members:
     :undoc-members:
-
-
diff --git a/test/orm/test_defaults.py b/test/orm/test_defaults.py
index 39f48bfcc..a2dc8cf45 100644
--- a/test/orm/test_defaults.py
+++ b/test/orm/test_defaults.py
@@ -42,6 +42,9 @@ class TriggerDefaultsTest(fixtures.MappedTest):
                 sa.schema.FetchedValue(for_update=True),
             ),
         )
+
+        dialect_name = testing.db.dialect.name
+
         for ins in (
             sa.DDL(
                 "CREATE TRIGGER dt_ins AFTER INSERT ON dt "
@@ -62,17 +65,37 @@ class TriggerDefaultsTest(fixtures.MappedTest):
                 ":NEW.col2 := 'ins'; :NEW.col4 := 'ins'; END;"
             ).execute_if(dialect="oracle"),
             sa.DDL(
+                "CREATE TRIGGER dt_ins BEFORE INSERT "
+                "ON dt "
+                "FOR EACH ROW "
+                "EXECUTE PROCEDURE my_func_ins();"
+            ).execute_if(dialect="postgresql"),
+            sa.DDL(
                 "CREATE TRIGGER dt_ins BEFORE INSERT ON dt "
                 "FOR EACH ROW BEGIN "
                 "SET NEW.col2='ins'; SET NEW.col4='ins'; END"
             ).execute_if(
                 callable_=lambda ddl, target, bind, **kw: bind.engine.name
-                not in ("oracle", "mssql", "sqlite")
+                not in ("oracle", "mssql", "sqlite", "postgresql")
             ),
         ):
-            event.listen(dt, "after_create", ins)
+            my_func_ins = sa.DDL(
+                "CREATE OR REPLACE FUNCTION my_func_ins() "
+                "RETURNS TRIGGER AS $$ "
+                "BEGIN "
+                "NEW.col2 := 'ins'; NEW.col4 := 'ins'; "
+                "RETURN NEW; "
+                "END; $$ LANGUAGE PLPGSQL"
+            ).execute_if(dialect="postgresql")
+            event.listen(dt, "after_create", my_func_ins)
 
-        event.listen(dt, "before_drop", sa.DDL("DROP TRIGGER dt_ins"))
+            event.listen(dt, "after_create", ins)
+        if dialect_name == "postgresql":
+            event.listen(
+                dt, "before_drop", sa.DDL("DROP TRIGGER dt_ins ON dt")
+            )
+        else:
+            event.listen(dt, "before_drop", sa.DDL("DROP TRIGGER dt_ins"))
 
         for up in (
             sa.DDL(
@@ -93,16 +116,34 @@ class TriggerDefaultsTest(fixtures.MappedTest):
             ).execute_if(dialect="oracle"),
             sa.DDL(
                 "CREATE TRIGGER dt_up BEFORE UPDATE ON dt "
+                "FOR EACH ROW "
+                "EXECUTE PROCEDURE my_func_up();"
+            ).execute_if(dialect="postgresql"),
+            sa.DDL(
+                "CREATE TRIGGER dt_up BEFORE UPDATE ON dt "
                 "FOR EACH ROW BEGIN "
                 "SET NEW.col3='up'; SET NEW.col4='up'; END"
             ).execute_if(
                 callable_=lambda ddl, target, bind, **kw: bind.engine.name
-                not in ("oracle", "mssql", "sqlite")
+                not in ("oracle", "mssql", "sqlite", "postgresql")
             ),
         ):
+            my_func_up = sa.DDL(
+                "CREATE OR REPLACE FUNCTION my_func_up() "
+                "RETURNS TRIGGER AS $$ "
+                "BEGIN "
+                "NEW.col3 := 'up'; NEW.col4 := 'up'; "
+                "RETURN NEW; "
+                "END; $$ LANGUAGE PLPGSQL"
+            ).execute_if(dialect="postgresql")
+            event.listen(dt, "after_create", my_func_up)
+
             event.listen(dt, "after_create", up)
 
-        event.listen(dt, "before_drop", sa.DDL("DROP TRIGGER dt_up"))
+        if dialect_name == "postgresql":
+            event.listen(dt, "before_drop", sa.DDL("DROP TRIGGER dt_up ON dt"))
+        else:
+            event.listen(dt, "before_drop", sa.DDL("DROP TRIGGER dt_up"))
 
     @classmethod
     def setup_classes(cls):
diff --git a/test/requirements.py b/test/requirements.py
index 6d4b3dc90..1c2561bbb 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -436,11 +436,6 @@ class DefaultRequirements(SuiteRequirements):
                 no_support("mysql", "requires SUPER priv"),
                 no_support("mariadb", "requires SUPER priv"),
                 exclude("mysql", "<", (5, 0, 10), "not supported by database"),
-                # huh?  TODO: implement triggers for PG tests, remove this
-                no_support(
-                    "postgresql",
-                    "PG triggers need to be implemented for tests",
-                ),
             ]
         )