Most of the time we need to know the number of records affected by insert or delete query in current session. Popular DBMS like SQL Server or MySQL offers built in mechanism to achieve this. For example, SQL Server has @@ROWCOUNT global variable and MySQL gets ROW_COUNT function that helps getting number of records affected in last query executed in current session. Redshift does not offer such built-in function to have this data. But by querying the system tables, we can get this information easily.

Getting number of records affected in last query executed in current session using system table

To get his number we need to use the following system tables of Redshift:

STV_SESSIONS: This table contains the active user sessions for Amazon Redshift.
STL_QUERY: the table only contain information about queries.
STL_INSERT: Contains insert execution steps for INSERT queries.

In order to get number of records affected in last query executed in current session for delete operation we need another system table named STL_DELETE. Alright, now we are familiar about some required system tables. It is time to build the query:

WITH inserted_result AS (
         SELECT i.tbl        AS tableid,
                SUM(i.rows)  AS total_affected_rows
         FROM   STV_SESSIONS s
                INNER JOIN stl_query q
                     ON  s.process = q.pid
                INNER JOIN stl_insert i
                     ON  i.query = q.query
         WHERE  i.rows > 0
         GROUP BY
                i.tbl
     )

SELECT DISTINCT t.name AS tablename,
       total_affected_rows
FROM   inserted_result ir
       INNER JOIN STV_TBL_PERM t
            ON  t.id = ir.tableid;

Testing the query

To test the query we will create an empty table like STV_TBL_PERM system table of redshift using the below command:

CREATE TABLE tmp_redshift_tableinfo (LIKE STV_TBL_PERM);

Open a new query editor and execute the scripts together like the image below.

Figure: Redshift Record affect by last query

The above scripts insert 100 rows from STV_TBL_PERM system table to the newly created tmp_redshift_tableinfo table. Afterwards, our query is getting number of records affected in last query executed in current session. Also, we can get this information for delete query by simply using STL_DELETE table in the above query instead of STL_INSERT table.

Hope this helps. Enjoy!!!