Dynamic from clause

Question

I have a table in which one column represent a name of a table in my db.

TableA

| tableName |
-------------
|  table 1  |
|  table 2  |
|  .......  |
|  table n  |

What I need to do is to count all the records that are inside each table listed in tableA.

What I managed to do so far is this:

select count(*)
from (
          select tableName
          from tableA
      ) tables

but this is wrong because it counts the number of rows in the tableA table, which is not what I need.

The list of table don't have any relationship pretty much so there are no join operations, I just need to add to a counter the number of all records in each table.

Can that be done with a plain sql query? I'm using postgresql but would like to come up with a solution that doesn't depend on any db vendor.

https://stackoverflow.com/a/44330331 – Sep 09 '22 at 09:20 — , Sep 09 '22 at 09:20

score 3 · Answer 1 · answered Sep 09 '22 at 09:26

3

select sum(row_count) as totalRecords
from(
    select table_schema, 
           table_name, 
           (xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count
    from (
       select table_name, table_schema, 
              query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count
         from information_schema.tables t
         join tableA ta on ta.tablename = t.table_name
         
      )t

    ) t

answered Sep 09 '22 at 09:26

Farshid Shekari

2,391
4
27
47

I tried and it works. Do you mind explaining what is going on here or giving some references? That would be really appreciated – User1254 Sep 09 '22 at 10:44
1

@User1254 When I saw your question and your problem, I thought we can handle it with information schema data and then google it and see how to get basic info about the table. you can look here https://stackoverflow.com/questions/2596670/how-do-you-find-the-row-count-for-all-your-tables-in-postgres also comment on your question. – Farshid Shekari Sep 09 '22 at 10:59
@User1254 if worked I'd be grateful to confirm it – Farshid Shekari Sep 09 '22 at 11:00

Thorsten Kettner · Answer 2 · 2022-09-09T10:08:12.837

0

A pure SQL solution that works in about any DBMS? Well, as you know you cannot simply query the table names from your control table and use these in a SQL FROM clause, because the table names in the control table are data while the table names in the FROM cause are structure, so to say.

As you should know your database, however, you can use the table names known to you and compare them to the entries in your control table:

select 'TABLE_1', count(*) from table_1 having 'TABLE_1' in (select tablename from tablea)
union all
select 'TABLE_2', count(*) from table_2 having 'TABLE_2' in (select tablename from tablea)
union all
select 'TABLE_3', count(*) from table_3 having 'TABLE_3' in (select tablename from tablea)
union all
select 'TABLE_4', count(*) from table_4 having 'TABLE_4' in (select tablename from tablea)
union all
select 'TABLE_5', count(*) from table_5 having 'TABLE_5' in (select tablename from tablea)

This query must be altered when new tables get added to the database. As adding tables to a database is something that happens seldom if ever, this shouldn't be an issue.

Demo: https://dbfiddle.uk/Dfk9nIFo

edited Sep 09 '22 at 10:08

answered Sep 09 '22 at 10:03

Thorsten Kettner

89,309
7
49
73

I didn't downvote this but I'm not sure if I can use it.. Unfortunately new tables are created quite often, enough for me to search for a "dynamic" approach instead of a "static" one, if you will. I'm trying to understand if the answer from @FarshidShekari is db vendor dipendent or not, didn't still understand what is going on there honestly – User1254 Sep 09 '22 at 10:24
1

Farshid Shekari's answer only works in PostgreSQL. There is no pure SQL approach other than the one I've shown that works independent from the DBMS. The only other solution would be not to use SQL alone. You can use some programming language like Java or C# with SQL, retrieve all database table names, select all table names in your control table, use your programming language to build the final SQL and execute this. – Thorsten Kettner Sep 09 '22 at 10:45
If tables get created quite often in the database this can be for two reasons mainly: **1.** It is a very young DB and you are still adding processes (e.g. you first only had products and clients, and then you add departments caring about the products, and then you add employees and managers working for the departments.) **2.** The database design is bad. Where you should just add data (e.g. another year, a new department) you must add a table due to bad design. – Thorsten Kettner Sep 09 '22 at 10:49
**3.** (I know I said two :-) Well, with a very big database with hundreds or thousands of tables and many, many processes, it is quite natural that processes get added or change from time to time, and hence new tables may be added every now and then. – Thorsten Kettner Sep 09 '22 at 10:53
At last: Someone must be adding those tables to the database, so why can't they also add them to the query at the same time? :-) – Thorsten Kettner Sep 09 '22 at 10:55
Yeah I get your point. Not sure if this db is designed in a bad way or not, unfortunately i'm not that good to understand this. The tables though are removed/added within a page that users can interact with so we should do something in that scenario but i don't think i can afford that.. :( – User1254 Sep 09 '22 at 11:01
@ThorstenKettner I didn't downvote to your answer. my approach needs a little change in other RDBMS and also it works in the same way. but in your way, suppose in the control table you have thousands of records, in this way you should write all of the to create a view or write a procedure to generate a dynamic query. – Farshid Shekari Sep 09 '22 at 11:12
@Farshid Shekari: PostgreSQL is very special in allowing this kind of query. In other DBMS "dynamic" means: have one query to select the tables, build the final query from the query results, execute the final query. – Thorsten Kettner Sep 09 '22 at 12:10
@ThorstenKettner I know it, other database has this feature to show information like rows count when you query catalogs (I mean user tables) like here https://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/ it's for SQL SErver – Farshid Shekari Sep 09 '22 at 12:16

Dynamic from clause

2 Answers2