postgresql stored procedure begin commit end

Question

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!

begin and end are just blocks in pl/pgsql. They have nothing to do with transactions. — Bergi
– Bergi, Commented Oct 25, 2021 at 1:54

Laurenz Albe · Accepted Answer · 2021-10-25 03:59:21Z

15

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).

answered Oct 25, 2021 at 3:59

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

hg628193hg Over a year ago

Thanks so much, i guessed there is no way to achieve ` begin; [code block1] end; begin; [code block2] end;` in plpgsql?

Laurenz Albe Over a year ago

Sure you can, if you omit the semicolons after BEGIN. But these are blocks, it has nothing to do with transaction management..

Collectives™ on Stack Overflow

postgresql stored procedure begin commit end

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related