My name is Takamichi Osumi, and I am part of the PostgreSQL Development Team. We are actively working on advancing PostgreSQL, along with talented and passionate community members around the world. Our team will be publishing blog articles focused on some of the functions and patches that our members worked on.
In this article, I will discuss CREATE OR REPLACE TRIGGER, which was committed for PostgreSQL 14, explaining why this feature was proposed and how the code was improved through discussion in the community.
Background – CREATE OR REPLACE TRIGGER and Oracle compatibility
PostgreSQL 13 only supported CREATE TRIGGER, which defines a trigger that will run the specified function when the associated event occurs.
Meanwhile, FUJITSU Enterprise Postgres has been offering users the extended syntax CREATE OR REPLACE TRIGGER 
See the full extended syntax in the
Application Development Guide , along with its DO clause since version 9.5. For PostgreSQL 14, Fujitsu proposed to implement OR REPLACE functionality in the community code, to reduce the amount of application development required for migration from Oracle to PostgreSQL, potentially leading to its increased adoption.
This proposal was successfully accepted and the patch was committed in November 2020.
PostgreSQL 13
DROP TRIGGER trg1 ON my_table;
CREATE TRIGGER trg1 BEFORE INSERT ON my_table FOR EACH ROW EXECUTE PROCEDURE my_function();
END;
PostgreSQL 14
CREATE OR REPLACE TRIGGER trg1 BEFORE INSERT ON my_table FOR EACH ROW EXECUTE PROCEDURE my_function();
END;
My main motivation to make the feature open to the OSS community was to facilitate the migration from Oracle to PostgreSQL. Whenever there are incompatibilities between databases, organizations face the cost of rewriting the application to make up for that incompatibility, creating a barrier for migration. As PostgreSQL expands its capabilities and provides features compatible with other proprietary databases, more users are likely to migrate to it, which works to grow PostgreSQL popularity. Committing CREATE OR REPLACE TRIGGER can be regarded as one of the activities to support this trend.
There have been several discussions in the community towards this trend, and our team pushed this movement forward by achieving the commit as a result of the project.
Functional overview – What is CREATE OR REPLACE TRIGGER?
CREATE TRIGGER simply creates a new trigger, whereas CREATE OR REPLACE TRIGGER can be used to either create a new trigger or overwrite an existing one.
Without implementation of the OR REPLACE clause, an error will occur if CREATE TRIGGER is run when a trigger with the same name already exists.
The beauty of CREATE OR REPLACE TRIGGER is that it takes care of creating a new trigger and replacing an existing one in a single statement. This eliminates the effort to write the two commands separately when migrating from Oracle databases. This results in reducing application migration effort. Additionally, in the context of construction of a new system, since the SQL expression is simplified, users will also achieve results with less time and effort.
Triggers are widely used in applications generally, and thus this feature will benefit users from all industries. CREATE OR REPLACE TRIGGER is especially powerful when your business logic in the application is updated frequently.
Behind the scenes
For this project, I had the chance to engage with Fujitsu PostgreSQL Development Team members from Fujitsu Australia Software Technology (FAST), who helped me to push this initiative through. FAST members were very helpful in reviewing everything, from improving the quality of the documentation and the source code to accurate test creation. This significantly reduced the time to successfully commit this feature into the community code. The contribution from FAST was invaluable in ensuring the successful completion of this project.
Also, this was my first ever contribution committed to the PostgreSQL code base. Personally, I was able to gain confidence and realization that I can contribute to the PostgreSQL world.
What motivates me to engage in community work is that the features developed there are made available to PostgreSQL users around the world. I'm very happy and excited to see the code that I conceptualized and coded is now embedded for the world to use.
In this project, I felt this especially when I saw my code replace triggers on a partitioned table Check our PostgreSQL Insider article
on PostgreSQL partitioning in PostgreSQL core successfully. Replacing triggers of partition tables was quite challenging, due to its internal complexity and thorough consideration required for many conditional branching patterns. It was hard work to finalize the optimal design, and I was really glad to achieve it with the advice from the community.
Looking into the future – triggers and more
In the past, Fujitsu has proposed to the community adding the OR REPLACE clause to CREATE TRIGGER with the support for the DO clause at the same time. This DO clause allows application developers to define a trigger with one-off temporary functions, which provides flexibility to applications. Defining DO enables applications to execute exceptional trigger actions for specific cases with ease. However, the OSS community preferred to implement the changes in this syntax step by step.
So as a start, Fujitsu went forward with OR REPLACE alone, without the DO clause. Persistent suggestion and successful discussion led to its acceptance in the community for PostgreSQL 14.
In the future, to extend CREATE OR REPLACE TRIGGER functionality further, it may be valuable to enable the DO clause next. The flexibility to applications described above will surely bring value to users.
We look forward to sharing further updates about our various projects in the PostgreSQL community in future articles.
