CODEWITHSUNDEEP
postgresqlpostgresql-11
Monku
Monku

Reputation: 2650

Run code block in a transaction in postgres

I want to run the following code block in a transaction so that if any of the sql statements fail the entire transaction is aborted. If I run the following block as it is, does it run in a transaction or do I need to run it inside BEGIN; ... COMMIT;

DO $$
  DECLARE
    readonly_exists int;
BEGIN
  SELECT COUNT(*) INTO readonly_exists FROM information_schema.enabled_roles
    WHERE role_name = 'readonly';
  IF readonly_exists = 0 THEN
    <SQL STATEMENT 1>
    <SQL STATEMENT 2>
    <SQL STATEMENT 3>
  ELSE
    RAISE EXCEPTION 'readonly role already exists';
  END IF;
END$$;

Upvotes: 2

Views: 2277

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246453

Any SQL statement always runs in a single transaction (the exception to this rule is CALL).

So your DO statement will run in a single transaction, and either all three SQL statements are successful, or all will be rolled back.

Upvotes: 5

Related Questions