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