2

There are many schemas and each schema has users table. I use different schemas for each organization/tenant. I want to get users count with COUNT function. How can I do that on all schemas?

i.e

schamas = [org1,org2,org3]
org1.tables = (users,videos,pictures)
org2.tables = (users,videos,pictures) 
org3.tables = (users,videos,pictures)

What I tried

select count(users) from *.users;
Luuk
  • 12,245
  • 5
  • 22
  • 33
Capitan Security
  • 77
  • 6

1 Answers1

0

Use scalar sub-queries. Using union CTEs for users, videos and pictures or PL/pgSQL are possible alternatives. Please note that the query below - not a very smart one indeed nor very efficient - has a regular structure and could be built mechanically by using a template.

select 
 (select count(*) from org1.users) + 
 (select count(*) from org2.users) + 
 (select count(*) from org3.users) as userscnt,
 (select count(*) from org1.videos) + 
 (select count(*) from org2.videos) + 
 (select count(*) from org3.videos) as videoscnt,
 (select count(*) from org1.pictures) + 
 (select count(*) from org2.pictures) + 
 (select count(*) from org3.pictures) as picturescnt;
MintBerryCRUNCH
  • 530
  • 4
  • 21
Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21
  • Thanks, But i want to use schema names dynamicly. Is it possible? – Capitan Security Oct 15 '22 at 17:07
  • 1
    Exactly what do you mean by dynamically? Pass a list of schema names or just not having to code each. Either way this requires dynamic SQL. – Belayer Oct 15 '22 at 17:50
  • Given that you have a list of schemata then you can easily built a query as the one above, either in the logic tier or in a PL/pgSQL function and run it as dynamic SQL as @Belayer noted. I would suggest however that you review your data design or you would need to do the above over and over again while reporting would be mere torture. – Stefanov.sm Oct 15 '22 at 19:44