PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – plpgsql does not handle exception

i have a table called stock_holdings which does not have data related to account_id and ticker_cd (actually it is empty):

select qty,total_amount from stock_holdings where account_id=1 and ticker_cd='XYZ';
 qty | total_amount
-----+--------------
(0 rows)

however when i run the following plpgsql code block, exception should handle the situation, however i get following results:

NOTICE:  before v_qty: -100
NOTICE:  before v_total_amount: -1000
NOTICE:  after v_qty: <NULL>
NOTICE:  after v_total_amount: <NULL>
DO

Query returned successfully in 70 msec.

code block looks like this:

do 
    language plpgsql
$$
declare
v_qty numeric := -100;
v_total_amount numeric := -1000;
begin
    raise notice 'before v_qty: %', v_qty;
    raise notice 'before v_total_amount: %', v_total_amount;
    SELECT qty,total_amount INTO v_qty,v_total_amount
    FROM stock_holdings
    WHERE account_id = 1 AND ticker_cd = 'XYZ';
    raise notice 'after v_qty: %', v_qty;
    raise notice 'after v_total_amount: %', v_total_amount;
exception
    when NO_DATA_FOUND then
    raise notice 'No data found error: %', sqlstate;
    when others then
    raise notice 'Other error: %', sqlstate;
end;
$$
Tags:

2

Answers


  1. 0

    For select to raise NO_DATA_FOUND you must use the strict option. The intent is when you expect exactly ONE row to be returned.

    If the STRICT option is specified, the command must return exactly one row or a run-time error will be reported, either NO_DATA_FOUND (no rows) or TOO_MANY_ROWS (more than one row)…

        SELECT qty,total_amount 
      INTO STRICT v_qty,v_total_amount
    FROM stock_holdings
    WHERE account_id = 1 
      AND ticker_cd = 'XYZ';

    Demonstration.

    Login or Signup to reply.
  2. 0

    You need to use the STRICT keyword, otherwise the expression will set the variables to NULL if no rows were returned by the query.

    eg.

    SELECT qty,total_amount INTO STRICT v_qty, v_total_amount FROM stock_holding;

    See https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

    Though you could also use the special FOUND variable, which is set to true if a query returns one or more rows. If you use STRICT keyword, then a TOO_MANY_ROWS exception is raised if the query returned more than one row.

    eg.

    SELECT qty,total_amount INTO v_qty, v_total_amount FROM stock_holding;
IF NOT FOUND THEN
    -- logic for handling if no value for v_qty and v_total_amount
END IF;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search