Redshift:特定用户的所有表的总行计数

Question

我们使用Periscope，它(我认为)根据我们赋予其访问权限的表中存在的总行数收费。

我的问题是:给定一个用户periscope，如何获得用户periscope有权访问的表中的总行数？

例如，如果用户periscope只能访问两个表:计数为8的t1和计数为2的t2，则总计数应为10。

我用这个answer得到了所有的表(不包括'pg_tables‘和'information_schema')，但是我不知道下一步该怎么做：

SELECT CAST(schemaname as varchar), CAST(  objectname as varchar)
FROM 
    (
    SELECT 
        schemaname
        ,objectname
        ,usename
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'select') AS sel
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'insert') AS ins
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'update') AS upd
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'delete') AS del
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'references') AS ref
    FROM
        (
        SELECT schemaname, 't' AS obj_type, tablename AS objectname, schemaname + '.' + tablename AS fullobj FROM pg_tables
        WHERE schemaname not in ('pg_internal')
        UNION
        SELECT schemaname, 'v' AS obj_type, viewname AS objectname, schemaname + '.' + viewname AS fullobj FROM pg_views
        WHERE schemaname not in ('pg_internal')
        ) AS objs
        ,(SELECT * FROM pg_user) AS usrs
    ORDER BY fullobj
    )
WHERE (sel = true or ins = true or upd = true or del = true or ref = true)
and usename = 'periscope'
and schemaname not in ('information_schema', 'pg_catalog');

如果你有一个优雅的解决方案，请让我知道！

Answer 1

这将为您提供所需的内容：

SELECT SUM(b.reltuples)
FROM 
(
    SELECT 
        schemaname
        ,objectname
        ,usename
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'select') AS sel
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'insert') AS ins
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'update') AS upd
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'delete') AS del
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'references') AS ref
    FROM
    (
        SELECT schemaname, 't' AS obj_type, tablename AS objectname, schemaname + '.' + tablename AS fullobj FROM pg_tables
        WHERE schemaname not in ('pg_internal')
        UNION
        SELECT schemaname, 'v' AS obj_type, viewname AS objectname, schemaname + '.' + viewname AS fullobj FROM pg_views
        WHERE schemaname not in ('pg_internal')
        ) AS objs
        ,(SELECT * FROM pg_user) AS usrs
    ORDER BY fullobj
) as a
JOIN 
(
    SELECT 
        nspname AS schemaname,relname,reltuples
    FROM pg_class C
    LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
    ORDER BY reltuples DESC
) as b
ON (a.schemaname = b.schemaname AND a.objectname = b.relname)
WHERE (sel = true or ins = true or upd = true or del = true or ref = true)
and usename = 'periscope'
and a.schemaname not in ('information_schema', 'pg_catalog');