如何检索除子表以外的所有表的行数 [英] How to retrieve row counts of all tables excluding child tables

问题描述

我不确定第二个查询为什么失败，我只在第二个查询中添加了caluse，因为我不需要子表的行数。

I am not sure why second query fails, I'm adding ONLY caluse in second query because i don't need child tables row count.

SQL正常工作

SELECT count(*) AS rows_cnt 
FROM (
  SELECT pgc.relname 
  FROM pg_class pgc 
  WHERE pgc.oid = 16424 
) as tblname 

SQL面对问题

SELECT count(*) AS rows_cnt 
FROM ONLY ( 
   SELECT pgc.relname 
   FROM pg_class pgc 
   WHERE pgc.oid = 16424 
) as tblname 

这里的表OID是 16424。

Here "16424" is my Table OID.

有人可以告诉我我的SQL怎么了。

Can anyone please tell me what is wrong with my SQL.

上述问题的更新：

SELECT count(*) AS rows_cnt 
FROM ONLY "test-1"  

'test -1'是oid 16424的表名，它工作正常，但是当我使用sub qu时动态生成表名会失败，并显示以下错误，不确定为什么。

'test-1' is the table name of oid 16424, and it is working properly, but when I use sub query to generate table name dynamically it fails with below error not sure why.

ERROR:  syntax error at or near "SELECT"
LINE 1: (SELECT count(*) AS rows_cnt FROM ONLY ( SELECT pgc.relname ...
                                             ^
********** Error **********

ERROR: syntax error at or near "SELECT"
SQL state: 42601
Character: 42

推荐答案

您无法使用静态SQL来完成所需的工作，为此您需要动态SQL。

You can't do what you want with static SQL, you need dynamic SQL for this.

最简单的方法是将其放入函数中：

The easiest way is to put this into a function:

create or replace function get_rowcounts(p_schema text)
  returns table (table_name text, row_count bigint)
as
$$
declare 
   name_rec record;
begin

   for name_rec in select t.table_name
                   from information_schema.tables t
                   where t.table_schema = p_schema
   loop
     return query execute format('select %L::text, count(*) from only %I.%I', name_rec.table_name, p_schema, name_rec.table_name);
   end loop;
end;
$$
language plpgsql;

可以按以下方式使用：

select *
from get_rowcounts('public');

这篇关于如何检索除子表以外的所有表的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！