Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 !


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux