Get Count of Records Affected by Insert or Update in Postgresql

How to get the row count of update,insert,delete query in postgresql

First, you are not able to count inserted/updated/deleted rows that way. The expression

count(row(new))

always returns 1 as row(new) is a single row.

Second, the records new and old are not set in a trigger for each statement. Per the documentation:

NEW

Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is unassigned in statement-level triggers and for DELETE operations.

PostgreSQL Return affected rows by UPDATE/INSERT query

I would create a function and have it either return text or an enumeration with the results. Here is an example:

CREATE OR REPLACE FUNCTION people.update_profile(USERID text,
    USER_NAME text, PHOTO_URL text)
  RETURNS text AS
$BODY$
DECLARE
  rec profile;
  matching_uid boolean;
  matching_name boolean;
  matching_both boolean;
  result text;
BEGIN

  for rec in select * from people.profile where user_id = USERID or USER_NAME = alias
  loop
    if rec.user_id = USERID and rec.alias = USER_NAME then matching_both = true;
    elsif rec.user_id = USERID then matching_uid = true;
    elsif rec.alias = USER_NAME then matching_name = true;
    end if;
  end loop;

  if matching_both then
    update people.profile
    set profile_picture = PHOTO_URL
    where user_id = USERID;

    result := 'Photo Updated';    
  elsif matching_name then
    result := 'Username is in use.  Please choose another';
  elsif matching_uid then
    update people.profile
    set profile_picture = PHOTO_URL, alias = USER_NAME
    where user_id = USERID;

    result := 'Photo and User Name Updated';
  else
    insert into people.profile
    (user_id, alias, profile_picture)
    values (USERID, USER_NAME, PHOTO_URL);

    result := 'New User Added';
  end if;

  return result;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

If you call the function like this:

select people.update_profile('hamb', 'Hambone', 'http://purple.com');

It would give you back the message of whether it succesfully updated, and if so what actions were taken.

You definitely can return a rowcount of what's impacted with something like this following the insert/update:

GET DIAGNOSTICS rowcount = ROW_COUNT;

But given that the function will only return a 1 or a 0, I think either the text or an enumeration of those messages would be more valuable.

Getting Affected Rows by UPDATE statement in RAW plpgsql

What you want is not currently possible in the form that you describe, but I think you can do what you want with UPDATE ... RETURNING. See UPDATE ... RETURNING in the manual.

UPDATE <target_table> 
SET Proprerty0 = Value0
WHERE <predicate>
RETURNING Property0;

It's hard to be sure, since the example you've provided is so abstract as to be somewhat meaningless.

You can also use a wCTE, which allows more complex cases:

WITH updated_rows AS (
    UPDATE <target_table> 
    SET Proprerty0 = Value0
    WHERE <predicate>
    RETURNING row_id, Property0
)
SELECT row_id, some_computed_value_from_property
FROM updated_rows;

See common table expressions (WITH queries) and depesz's article on wCTEs.

---

UPDATE based on some added detail in the question, here's a demo using UPDATE ... RETURNING:

CREATE TABLE upret_demo(
  id serial primary key,
  somecol text not null,
  last_updated timestamptz
);

INSERT INTO upret_demo (somecol, last_updated) VALUES ('blah',current_timestamp);

UPDATE upret_demo
SET
  somecol = 'newvalue',
  last_updated = current_timestamp
WHERE last_updated = '2012-12-03 19:36:15.045159+08'    -- Change to your timestamp
RETURNING 
  somecol || '_computed' AS a,
  'totally_new_computed_column' AS b;

Output when run the 1st time:

         a         |              b              
-------------------+-----------------------------
 newvalue_computed | totally_new_computed_column
(1 row)

When run again, it'll have no effect and return no rows.

If you have more complex calculations to do in the result set, you can use a wCTE so you can JOIN on the results of the update and do other complex things.

WITH upd_row AS (
  UPDATE upret_demo SET 
    somecol = 'newvalue',
    last_updated = current_timestamp
  WHERE last_updated = '2012-12-03 19:36:15.045159+08'
  RETURNING id, somecol, last_updated
)
SELECT
  'row_'||id||'_'||somecol||', updated '||last_updated AS calc1,
 repeat('x',4) AS calc2
FROM upd_row;

In other words: Use UPDATE ... RETURNING, either directly to produce the calculated rows, or in a writeable CTE for more complex cases.

Postgres CTE Insert and get count

According to Postgres document all sub-statements of a query with CTEs happen virtually at the same time. I.e., they are based on the same snapshot of the database.

You would need two statements (in a single transaction) for what you are trying to do OR calculate with total data when inserted in CTE:

WITH reply_data(id, threadid, commentid, userid, description, created, updated) AS (
    VALUES ('27c12e17-b105-48fd-897b-82e5965ab15a'::uuid,
            'bbe04e77-0e53-4716-b001-81e7dbf40d70'::uuid,
            'fd2513fb-5e92-4a40-a295-6c122c325166'::uuid,
            '5b3a6120-233e-4b77-9160-c08c484db31b'::uuid,
            'Manual Reply to comment from SQL',
            now(),
            now())
),
     reply_insert AS (
         INSERT INTO replies (id, threadid, commentid, userid, description, created, updated)
             SELECT rd.id, rd.threadid, rd.commentid, rd.userid, rd.description, rd.created, rd.updated
             FROM reply_data rd
             RETURNING id, commentid
     ),
     user_reply_insert as (
         INSERT INTO user_replies (userid, replyid)
             SELECT rd.userid, rd.id FROM reply_data rd
             RETURNING userid
     ),
     replyCount as (
         select count(*) + (select count(*) from reply_insert) as repliescount
         from replies r,
              reply_data rd
         where r.commentid = rd.commentid
     )

SELECT repliescount FROM replyCount;

Getting the number of the rows affected by update or insert

When function/trigger is called, the new internal execution state structure is created (PLpgSQL_execstate) with ROW_COUNT value (eval_processed property) set to 0. So inside function you can get ROW_COUNT only for statements inside the same function. As a workaround you can pass sql statement as the text argument to this procedure and execute it inside.

Postgresql function return affected row count

you should look into GET DIAGNOSTICS, since this is a PLpgSQL function. You might also find the Postgres SQL extension RETURNING * for UPDATE/INSERTS useful.

CREATE OR REPLACE FUNCTION update() RETURNS void  AS 
$BODY$
DECLARE
  a_count integer;
  b_count integer;
BEGIN
   update test_a set name='cde' where name='abc';
   GET DIAGNOSTICS a_count = ROW_COUNT;
   update test_b set name='mno' where name='klm';
   GET DIAGNOSTICS b_count = ROW_COUNT;

   RAISE NOTICE 'The rows affected by A=% and B=%', a_count, b_count ;
END;
$BODY$   
LANGUAGE plpgsql VOLATILE   
COST 100; 
ALTER FUNCTION update()   
OWNER TO postgres;

Depending on what you might want to achieve, the special boolean variable "FOUND" could serve; UPDATE, INSERT, and DELETE statements set FOUND true if at least one row is affected, false if no row is affected.

---

Related Topics