postgresql function - get number of rows affected by update query [duplicate]

Question

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;

postgresql.org/docs/current/static/…

Vao Tsun
– Vao Tsun

2018-04-23 09:46:53 +00:00
Commented Apr 23, 2018 at 9:46 — Vao Tsun
– Vao Tsun, Commented Apr 23, 2018 at 9:46
select number of affected rows to a variable in function

DEarTh
– DEarTh

2018-04-23 09:53:03 +00:00
Commented Apr 23, 2018 at 9:53 — DEarTh
– DEarTh, Commented Apr 23, 2018 at 9:53

Vao Tsun · Accepted Answer · 2018-04-23 09:50:53Z

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

answered Apr 23, 2018 at 9:50

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nadhas Over a year ago

I'm executing update query followed by a select query. Its always returning row count as 1 even update query is failed.

Vao Tsun Over a year ago

you should share the code. put get diagnostics STRAIGHT after update, otherwise you get other rows count

Collectives™ on Stack Overflow

postgresql function - get number of rows affected by update query [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related