Oracle vs PostgreSQL – Transaction control statements
While migrating from Oracle to PostgreSQL , the important fact to consider is that they both are 2 different database engines. The major mistake made by organizations during the course of migration is assuming that the behavior of both Oracle and PostgreSQL are same. One of such areas with differences between Oracle vs PostgreSQL is while working with Transaction control statements. While migrating PL/SQL procedures and functions from Oracle to PL/pgSQL in PostgreSQL, we need to understand in detail about how transaction control statements work. By the end of this article, you would have clarity about Oracle vs PostgreSQL - Transaction control statements.
Let us start with an important fact that, BEGIN and END in PL/SQL and PL/pgSQL are merely syntactic elements, and have nothing to do with transactions. Most of the time, we get confused with the similarly-named SQL commands for transaction control. The BEGIN that starts a block in PL/SQL and PL/pgSQL is different from the SQL statement BEGIN that starts a transaction. BEGIN/END here are used for grouping purposes only but they do not start or end a transaction. Functions and trigger procedures are always run as part of a transaction started by an outer query. They can't start or commit that transaction because there's no context in which they have to run.
We can conclude from the above output that BEGIN and END do not start or end transactions. The same is with PL/SQL in Oracle.
