sql - 亚马逊 Redshift 中的上次更新查询计数

Question

有没有办法计算通过 amazon redshift 中的 SQL 更新查询影响的最后行数？

类似于 PG_LAST_COPY_COUNT() 之类的函数，用于计算亚马逊 Redshift 中的最后一个副本计数。

任何帮助表示赞赏。

Answer 1

这应该给信息

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();