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

\set AUTOCOMMIT on
create table t(n integer);

create or replace procedure p2()
  language plpgsql
as $$
begin
  raise notice 'txid in "p2()" at start ............. %', txid_current();
  -- This "rollback" is critical.
  -- Else "SET TRANSACTION ISOLATION LEVEL must be called before any query".
  -- Notice that "txid_current()" seems to count as  "query".
  rollback;

  set transaction isolation level repeatable read;
  declare
    levl varchar(20) not null := '?';
  begin
    show transaction isolation level into levl;
    raise notice 'txid in "p2()" after "set txn"....... %, level = %', txid_current(), levl;
  end;
  insert into t(n) values(17);
  rollback;

  set transaction isolation level serializable;
  declare
    levl varchar(20) not null := '?';
  begin
    show transaction isolation level into levl;
    raise notice 'txid in "p2()" after "set txn"....... %, level = %', txid_current(), levl;
  end;
  insert into t(n) values(42);
  commit;
end;
$$;

\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
