psql (9.6.7, server 9.5.2) on linux
How does one get the status of an sql statement executed in plpgsql? If that status is cryptic, how can that be translated to something which someone could understand? Finally, how can I effectively do a start transaction and either rollback or commit based on the results of the sql statements run?
create or replace function xfer_savings_to_checking(acct_no text, howmuch float)
returns text as $$
declare
savings_balance float;
checking_balance float;
begin
select balance into savings_balance from savings_acct_info where acct = acct_no;
sql_status = <capture the status>
if(something_went_wrong) then
raise exception 'select savings statement was bad "%"',sql_status;
return 'error';
end if;
select balance into checking_balance from checking_acct_info where acct = acct_no;
sql_status = <capture the status>
if(something_went_wrong) then
raise exception 'select checking statement was bad "%"',sql_status;
return 'error';
end if;
if(howmuch > saving_balance) then
raise notice 'Hey, you dont have that much to xfer ! You only have %',savings_balance;
return 'error';
end if;
start transaction;
update savings_acct_info set balance = balance - howmuch where acct = acct_no;
sql_status = <capture the status>
if(something_went_wrong) then
raise exception 'updating savings acct "%"',sql_status;
rollback;
return 'error';
end if;
update checking_acct_info set balance = balance + howmuch where acct = acct_no;
sql_status = <capture the status>
if(something_went_wrong) then
raise exception 'updating checking acct "%"',sql_status;
rollback;
return 'error';
end if;
commit;
end;
$$ language plpgsql;
Of course I don't know what the <capture the status> and "something_went_wrong" pieces look like, or they even make sense with how this sort of thing shold be properly handled in plpgsql. Also, in my trials, it appears that plpgsql doesn't like "start transaction". So how is that piece done ?
Thanks in Advance for any help !