We are trying to emulate a proprietary DB, which has "global primary keys", with PostgreSQL. That is what we would have liked to do (for many rows of many tables):
select 'some_table' as table_name, t.*
from some_table t
where id = 1
union all
select 'other_table' as table_name, t.*
from other_table t
where id = 2;
But, of course, that doesn't work, because every table schema is different. Our DBA suggested this instead:
select 'some_table' as table_name, jsonb_agg(t.*) as content
from some_table t
where id = 1
union all
select 'other_table' as table_name, jsonb_agg(t.*) as content
from other_table t
where id = 2;
Which would do it, but we're worried about the amount of data produced/transfered. Basically, every row of each table is returned as one JSONB object contained in a single array. Now what bothers us, is the repetition of the column names for each row.
Could we instead somehow return a JSONB array per row, with only the values, based on the order of the columns in each table?