Getting the total size of rows with same ids across multiple tables restricted to a particular user id

Question

I'm trying to calculate all the projects size of one user across multiple tables.

a unique project is separated into 4 tables with multiple rows assigned to it.

for the moment I'm only able to calculate one, here is what I have so far:

select  pg_size_pretty((
  select COALESCE(sum(pg_column_size(s)), 0) as ts
  from editor_s s
  where project_uid = 'z_5oyUFV6615Lh1d-FabX' )
  +
  ( select COALESCE(sum(pg_column_size(o)), 0) as os
  from editor_o o
  where project_uid = 'z_5oyUFV6615Lh1d-FabX' )
  +
  ( select COALESCE(sum(pg_column_size(e)), 0) as es
  from editor_c e
  where project_uid = 'z_5oyUFV6615Lh1d-FabX' )
  +
  ( select COALESCE(sum(pg_column_size(ss)), 0) as sss
  from editor_ss ss
  where project_uid = 'z_5oyUFV6615Lh1d-FabX' ))

as total

So let's say I want all projects sizes of user 3 I would like to receive back

|-------------------|
|project_uid|size   |
|-------------------|
| 1234ua    | 20kb  |
|-------------------|
| ze64ua    | 120kb |
|-------------------|
| bv76y1    | 93kb  |
|-------------------|

is this possible?

EDIT

As it would take a huge amount of time to reproduce the issue and create examples and as we are close to the point I'll try describe a bit more my attempt:

So one project has its data separated into 4 tables each project can have one or many rows refering to it's project_uid in a table.

1. table1 have composite primary key user_uid and project_uid ( 1 row per project )
2. table2 have composite primary key uid, user_uid and project_uid( multiple rows per project )
3. table3 have composite primary key uid, user_uid and project_uid
4. table4 have composite primary key uid, user_uid and project_uid

uid and project_uid are of type TEXT where user_uid is of type UUID

all tables have different numbers of columns with different data types (TEXT, JSONB, FLOAT...)

So what I would like is get all the projects total sizes ( across all 4 tables ) grouped by project_uid and where user_uid = 'SOME_USER_UID'

so in pseudo code this would look something like ( and im sorry but I'm a newby in SQL databases so I'll write it as clear as I can )

PSEUDO CODE:

SELECT SUMS(
  SELECT ( 

    sum(pg_column_size(table1)),

    sum(pg_column_size(table2)),

    sum(pg_column_size(table3)),

    sum(pg_column_size(table4))

  ) GROUP BY project_uid ) WHERE user_uid = 'SOME USER UID';

ps: I've invented the operator SUMS for this example that would return multiple sums (rows)

Answer 1

Some guessing:

SELECT project_uid, sum(col_size) 
FROM (
    SELECT project_uid, COALESCE(sum(pg_column_size(s)), 0) as col_size
    FROM editor_s s
    GROUP BY project_uid

    UNION ALL

    SELECT project_uid, COALESCE(sum(pg_column_size(o)), 0) as col_size
    FROM editor_o o
    GROUP BY project_uid

    UNION ALL

    ...

) AS t
GROUP BY project_uid

Answer 2

I've managed to make it work by doing:

select project_uid, sum(col_size) FROM (
  (select COALESCE(sum(pg_column_size(s)), 0) as col_size, project_uid
  from editor_s s
  where user_uid = 'some_user_uid'
  group by project_uid )
  UNION ALL
  ( select COALESCE(sum(pg_column_size(o)), 0) as col_size, project_uid
  from editor_o o
  where user_uid = 'some_user_uid'
  group by project_uid )
  UNION ALL
  ( select COALESCE(sum(pg_column_size(e)), 0) as col_size, project_uid
  from editor_c e
  where user_uid = 'some_user_uid'
  group by project_uid )
  UNION ALL
  ( select COALESCE(sum(pg_column_size(ss)), 0) as col_size, project_uid
  from editor_ss ss
  where user_uid = 'some_user_uid'
  group by project_uid )
) 
 as total group by project_uid;

Thank you to @Lennart