Error in postgreSQL function

CREATE OR REPLACE FUNCTION testtable()
RETURNS void AS
'
BEGIN
  FOR i IN 2..41 LOOP
    FOR j IN 1..5 LOOP
        IF j = 1 THEEN
            INSERT INTO a(test1, test2)VALUES(i,1);
        ENDIF;
        INSERT INTO b(test3, test4) VALUES(i,j);
    END LOOP;
  END LOOP;
  COMMIT;
END;
'
 LANGUAGE plpgsql;
select testtable();

This error will occur

cannot begin/end transactions in PL/pgSQL

I'm in trouble because I don't know the cause
Please let me know.

sql postgresql

2022-09-30 21:34

1 Answers

The reason is that there is COMMIT in the function.
As you can see in the error message, PostgreSQL does not allow transaction control, such as commit or rollback, within user-defined functions.

From https://www.postgresql.jp/document/10/html/plpgsql-structure.html:

The BEGIN/END of PL/pgSQL is simply for consolidation and does not start or end a transaction. Functions and trigger procedures are always performed inside transactions established by external queries. There is no context in which transactions can be performed, so they cannot be initiated or committed.

From PostgreSQL 11, you can create a procedure with the CREATE PROCEDURE statement and call it CALL.

https://www.postgresql.org/docs/11/static/plpgsql-transactions.html

2022-09-30 21:34

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656