-- Using PostgreSQL 11.2 on macOS Mojave Version 10.14.6
-- At the o/s prompt:
-- psql -h 127.0.0.1 -p 5432 -d postgres -U postgres -f txn_control_in_plpgsql_proc.sql
-- Can't re-direct to file 'cos 'raise notice' still comes to the screen.

\echo
\echo ------------------------------------------------------------
\echo Setup.
\echo
\set AUTOCOMMIT on
create table t(n integer);

create or replace procedure p1()
  language plpgsql
as $$
begin
  insert into t(n) values(17);
end;
$$;

create or replace procedure p2()
  language plpgsql
as $$
  declare
    levl_1 varchar(20);
    levl_2 varchar(20);
  begin
    -- This "rollback" is critical.
    -- Else "SET TRANSACTION ISOLATION LEVEL must be called before any query".
    rollback;

    set transaction isolation level repeatable read;
    show transaction isolation level into levl_1;
    insert into t(n) values(17);
    rollback;

    set transaction isolation level serializable;
    show transaction isolation level into levl_2;
    insert into t(n) values(42);
    commit;

    raise notice 'isolation level #1: %', levl_1;
    raise notice 'isolation level #2: %', levl_2;
  end;
$$;

\echo
\echo ------------------------------------------------------------
\echo Test p1() with AUTOCOMMIT off
\echo

\set AUTOCOMMIT off
-- get clean start
begin;
delete from t;
commit;

call p1();
select n from t order by n;
rollback;
select n from t order by n;
rollback;

\echo
\echo ------------------------------------------------------------
\echo Test p1() with AUTOCOMMIT on
\echo

\set AUTOCOMMIT on
-- get clean start
begin;
delete from t;
commit;

call p1();
select n from t order by n;
\echo Expect "WARNING: there is no transaction in progress"
rollback;

\echo
\echo ------------------------------------------------------------
\echo Test p2() with AUTOCOMMIT on. Works only in this mode.
\echo Else "ERROR: invalid transaction termination"
\echo from the first "rollback" statement.
\echo

\set AUTOCOMMIT on

-- get clean start
begin;
delete from t;
commit;

call p2();
select n from t order by n;
