How to get row count on multiple schemas with same table name-CodePudding

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;

CodePudding user response：

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;