Last update query count in amazon redshift

Question

Is there any way to count last number of rows affected through SQL update query in amazon redshift ?

Similar to function like PG_LAST_COPY_COUNT() to count last copy-count in amazon redshift.

Any help is appreciated.

Answer 1

This should give the information

SELECT CASE
         WHEN d.relname IS NOT NULL THEN d.relname
         ELSE i.relname
       END TableName,
       CASE
         WHEN nvl (Deleted_Rows,0) = 0 THEN nvl (Inserted_Rows,0)
         ELSE 0
       END Inserted_Rows,
       CASE
         WHEN nvl (Deleted_Rows,0) = nvl (Inserted_Rows,0) THEN nvl (Inserted_Rows,0)
         ELSE 0
       END Updated_Rows,
       CASE
         WHEN nvl (Inserted_Rows,0) = 0 THEN nvl (Deleted_Rows,0)
         ELSE 0
       END Deleted_Rows
FROM (SELECT query,
             relname,
             SUM(ROWS) Deleted_Rows
      FROM stl_delete sd
        JOIN pg_class t ON t.oid = sd.tbl
      GROUP BY query,
               relname) d
  FULL JOIN (SELECT query,
                    relname,
                    SUM(ROWS) Inserted_Rows
             FROM stl_insert si
               JOIN pg_class t ON t.oid = si.tbl
             GROUP BY query,
                      relname) i ON i.query = d.query
WHERE CASE WHEN d.query IS NOT NULL THEN d.query ELSE i.query END = pg_last_query_id();

Answer 2

You are correct, there is no equivalent of PG_LAST_COPY_COUNT() for capturing the last number of affected rows from a insert/update/delete however you can achieve this by using the system tables STV_SESSIONS, STL_QUERY, and STL_INSERT. There's a great blog post here about how to build the query.

Hope this helps!