英文:
how do i loop through tables to get counts
问题
以下是您提供的内容的翻译部分：
I have tried to use the following, but it seems that different nodes cannot be mixed:
我尝试使用以下代码，但似乎不能混合不同的节点：
WITH tables_i_want AS (
SELECT *, table_schema||'.'||table_name as tbl FROM temp.redshift_mod_dates WHERE table_schema = 'whatever'
)
SELECT nspname
FROM pg_catalog.pg_class AS c
JOIN pg_catalog.pg_namespace AS ns
ON c.relnamespace = ns.oid
INNER JOIN tables_i_want as tiw
ON tiw.tbl = c.oid
AND relname not like 'pg_%'
so, then I tried a procedure:
因此，我尝试了一个存储过程：
CREATE OR REPLACE PROCEDURE f_test()
LANGUAGE plpgsql
AS $$
DECLARE
full_table_name1 VARCHAR;
full_table_name VARCHAR;
BEGIN
FOR full_table_name IN (SELECT table_schema||'.'||table_name as full_table_name FROM temp.redshift_mod_dates WHERE table_schema = 'whatever')
LOOP
EXECUTE 'SELECT INTO temp.redshift_tables_with_cnt %, COUNT(*) FROM %', full_table_name;
RAISE INFO '%', full_table_name;
END LOOP;
END;
$$;
seems there's an issue with the variable:
似乎变量存在问题：
[42601] ERROR: syntax error at or near "$1" Where: SQL statement in PL/PgSQL function "f_test" near line 5
英文:
I have tried to use the following, but it seems that different nodes cannot be mixed:
WITH tables_i_want AS (
SELECT *, table_schema||'.'||table_name as tbl FROM temp.redshift_mod_dates WHERE table_schema = 'whatever'
)
SELECT nspname
FROM pg_catalog.pg_class AS c
JOIN pg_catalog.pg_namespace AS ns
ON c.relnamespace = ns.oid
INNER JOIN tables_i_want as tiw
ON tiw.tbl = c.oid
AND relname not like 'pg_%'
so, then I tried a procedure:
CREATE OR REPLACE PROCEDURE f_test()
LANGUAGE plpgsql
AS $$
DECLARE
full_table_name1 VARCHAR;
full_table_name VARCHAR;
BEGIN
FOR full_table_name IN (SELECT table_schema||'.'||table_name as full_table_name FROM temp.redshift_mod_dates WHERE table_schema = 'whatever')
LOOP
EXECUTE 'SELECT INTO temp.redshift_tables_with_cnt %, COUNT(*) FROM %', full_table_name;
RAISE INFO '%', full_table_name;
END LOOP;
END;
$$;
seems there's an issue with the variable:
> [42601] ERROR: syntax error at or near "$1" Where: SQL statement in PL/PgSQL function "f_test" near line 5
答案1
得分: 1
如果您想要获取所有表的row-count，您可以使用以下查询来实现：
select tab.table_schema,
tab.table_name,
tinf.tbl_rows as rows
from svv_tables tab
join svv_table_info tinf
on tab.table_schema = tinf.schema
and tab.table_name = tinf.table
where tab.table_type = 'BASE TABLE'
and tab.table_schema not in('pg_catalog','information_schema')
and tinf.tbl_rows > 1
order by tinf.tbl_rows desc;
您可以将数据存储到临时表中，然后根据需要将其移动到持久表或进行进一步的处理。
英文:
If you want to receive the row-count for all the tables you could achieve it using the following query
select tab.table_schema,
tab.table_name,
tinf.tbl_rows as rows
from svv_tables tab
join svv_table_info tinf
on tab.table_schema = tinf.schema
and tab.table_name = tinf.table
where tab.table_type = 'BASE TABLE'
and tab.table_schema not in('pg_catalog','information_schema')
and tinf.tbl_rows > 1
order by tinf.tbl_rows desc;
You can have the data stored into a temporary table and then move them to a persistant table or do further processing as required.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论