procedures and transactions

[Rob Nikander]

Hi,

I’m trying to understand how procedures work with transactions. I tried the 
code below - it’s a simple procedure to print some notices and commit a 
transaction. If I call it from psql after a `begin`, then it gives an error. 
What does that error mean? Are procedures not allowed to commit/rollback if 
they are called within in an outer transaction?
Also, I tried putting a `start transaction` command in the procedure. I got 
another error: `unsupported transaction command in PL/pgSQL`. Are procedures 
not allowed to start transactions? Or is there another command?

thanks,
Rob

create or replace procedure t_test(n integer)
as $$
begin
    raise notice 'current isolation level: %', (select 
current_setting('transaction_isolation'));
    raise notice 'current txid: %', (select txid_current());
    raise notice '---';
    commit;
    raise notice 'current isolation level: %', (select 
current_setting('transaction_isolation'));
    raise notice 'current txid: %', (select txid_current());    
end;
$$ language plpgsql;

psql> begin;
psql> call t_test(1);

NOTICE:  current isolation level: read committed
NOTICE:  current txid: 111490
NOTICE:  ---
ERROR:  invalid transaction termination