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:
NOTICE: after v_total_amount:
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;
$$