SQL enhancements
================

EDB Postgres Extended Server includes a number of SQL enhancements.

Rollback options
----------------

In PostgreSQL, any error in a transaction rolls back all actions by that
transaction. This behavior is different from other DBMS, such as Oracle
and SQL Server, where an error causes rollback of only the last
statement. This difference in transaction handling semantics doesn’t
cause a problem in all cases, but it does make implementing business
logic in PostgreSQL difficult for Oracle Database and Microsoft SQL
Server developers.

One workaround is to manually introduce a savepoint, internally known as
subtransactions, into the application code. This is time consuming and
difficult to test. A savepoint is an additional statement and therefore
increases transaction latency. Given the overhead of additional
development work and slower performance, this approach isn’t viable in
most cases.

EDB Postgres Extended Server allows you to roll back just the current
statement. The statement-level rollback feature provides an optional
mode to choose whether to allow rollback of the whole transaction or
just the current statement. No manual recoding is required. There’s some
added overhead, but it’s lower than for a savepoint.

See :ref:`transaction_rollback_scope <transaction_rollback_scope>`  for information on setting the transaction rollback
scope inside the database and :ref:`JDBC properties for rollback scope <JDBC properties for setting rollback scope>`  for information on
continuing past an error on a JDBC batch job.

Cursors with prepared statements
--------------------------------

EDB Postgres Extended Server allows declaring a cursor over a previously
created prepared statement.

For example:

::

   PREPARE foo AS ...; DECLARE c1 CURSOR FOR foo;

PL/pgSQL compatibility
----------------------

EDB Postgres Extended Server integrates with other migration tools with
a number of PL/pgSQL compatibility features.

For general simplicity, EDB Postgres Extended Server allows calling
functions using plpqsl without the PERFORM keyword.

For example,

::

   BEGIN somefunc(); END

Where ``somefunc`` is not a keyword.

.. toctree::
  :maxdepth: 3

  Reference--sql_features--txnrollback
  Reference--sql_features--jdbctxnrollback