PostgreSQL: Can’t update a table without finishing execution of function

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Before reading this article, I would suggest please read about MVCC because of PostgreSQL based on MVCC architecture.

What is Multi Version Concurrency Control (MVCC)

The day before yesterday, one of our developers asked a question like I want updated data on a table while a function is running in PostgreSQL.

If you are from Microsoft SQL Server background, of course, you will need this because you can get an updated data on a table while a function is running. Why? Because SQL Server is auto-committed by default.

When you create a function in PostgreSQL, you must put all your code between BEGIN and END. In PostgreSQL, it treats as transaction block, and you can get only updated table data once function execution completed or you can get the previous state of your table if execution of a function failed.

Why? because of MVCC architecture you cannot get partially updated table otherwise other users can update those value which breaks the rule of MVCC.

While function is running, a user can still access the old records of a table, and once function execution got completed, a user can access the newly updated records of a table.

Then, they asked one more question like If the function code is default between BEGIN and END transaction block when we should define explicitly BEGIN TRANSACTION and COMMIT TRANSACTION in a PostgreSQL function?

My answer was, you can also set BEGIN TRANSACTION and COMMIT TRANSACTION explicitly when you want ROLLBACK or SAVEPOINT action.
But if you do not define this, by default everything is in transaction block and you will get all the output once your function completes its execution.