How can I get and handle the status of sql statements that run in plpgsql ?

From: David Gauthier <davegauthierpg(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How can I get and handle the status of sql statements that run in plpgsql ?
Date: 2018-10-02 20:47:22
Message-ID: CAMBRECCnr_xUg0iUDk4-ZZLc1NTFbd48SX8wYRfHnjKcQJh-6w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi:
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 !

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Christopher Browne 2018-10-02 21:18:42 Re: How can I get and handle the status of sql statements that run in plpgsql ?
Previous Message Martin Mueller 2018-10-02 20:38:36 metadata about creation and size of tables