postgresql stored procedure begin commit end-CodePudding

I'm actually very confused when performing postgresql stored procedure (I learned the below from somewhere):

create or replace procedure update_dba_trades ()
language plpgsql
as $$
begin
    [CODE BLOCK WITH INSERT DELETE ETC...]
    commit;

end;
$$

Why do we use all begin, end and commit? My understanding of postgresql is "end" is the same as "commit"

begin;
[code block]
end;

represents one complete transaction (either all failed or all succeed). I don't need to begin; [code]; commit; end;

However, I have difficulties trying to implement multiple independent code blocks. In PostgreSQL, I could do

begin;
[code block1]
end;
begin;
[code block2]
end;

Then [code block1] can succeed even if [code block 2] failed and wise versa. If I do,

create or replace procedure update_dba_trades ()
language plpgsql
as $$
begin;
[code block1]
end;
begin;
[code block2]
end;
$$

Then there is error. How do I achieve multiple independent code block? Thanks!

CodePudding user response：

Don't mix up the SQL statements BEGIN (synonymous with START TRANSACTION) and END (synonymous with COMMIT) with the PL/pgSQL syntax elements BEGIN and END.

In PL/pgSQL, code is arranged in blocks that look like this:

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ];

BEGIN and END are not statements here, they are rather like { and } in programming languages like C or Java and indentation in Python.

You can use transaction control SQL statements in a PL/pgSQL procedure, but you cannot explicitly start a transaction, since you are already in one, so the BEGIN SQL statement is not allowed. A new transaction is automatically started in PL/pgSQL if you end the previous one, which has to be done with COMMIT (END is not allowed as synonym for COMMIT in PL/pgSQL).