2

I'm writing one postgresql function that executes some update query. I need to make sure that any rows affected or not and return success/failure message accordingly.

If(rows affected)? then 'update success' else 'update failed'

How to RAISE NOTICE the row count??

-- Get count from UPDATE
WITH rows AS (
    UPDATE distributors
    SET dname = 'JKL Widgets'
    WHERE did <= 10
    RETURNING 1
)
SELECT count(*) FROM rows;
Nadhas
  • 5,421
  • 2
  • 28
  • 42
  • https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-CALL-STACK – Vao Tsun Apr 23 '18 at 09:46
  • [select number of affected rows to a variable in function](https://dba.stackexchange.com/questions/131825/how-to-select-number-of-affected-rows-to-variable-in-function) – DEarTh Apr 23 '18 at 09:53

1 Answers1

4

use get diagnostics, e.g:

t=# create table so17(i int);
CREATE TABLE
t=# insert into so17 values(0);
INSERT 0 1
t=# do $$
declare d int;
begin
update so17 set i = 0 where i=0;
get diagnostics d = row_count;
raise info 'updated: % rows', d;
update so17 set i = 0 where i=1;
get diagnostics d = row_count;
raise info 'updated: % rows', d;
end;
$$;
INFO:  updated: 1 rows
INFO:  updated: 0 rows
DO
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • I'm executing update query followed by a select query. Its always returning row count as 1 even update query is failed. – Nadhas Apr 23 '18 at 10:14
  • you should share the code. put get diagnostics STRAIGHT after update, otherwise you get other rows count – Vao Tsun Apr 23 '18 at 10:22