CODEWITHSUNDEEP
sqlpostgresql
user9355500
user9355500

Reputation: 5

Union different tables

The following function joins dynamically different tables.

create or replace function unified_tables() returns table(  
                      1         TEXT
                    , 2         TEXT
                    , 3         TEXT
                    , 4         TEXT
                    , 5         JSONB
                    , 6         BIGINT
                    , 7         BIGINT
                    , 8         TEXT
                    , 9         TEXT
                    , 10        TIMESTAMPTZ
                    )
as
$$
declare
  a record;
begin
  for a in select table_schema
                    from information_schema.tables
                    where table_name = 'name' 
  loop
     return query 
        execute format('select  %L as source_schema,  *  from %I.name', a.table_schema,  a.table_schema);
  end loop;
end;
$$
language plpgsql;

Unfortunately, not all the tables called have all the columns specified in RETURNS TABLE.

Precisely, there are 15 tables (the loop goes over 200+ tables) missing the column 2, two tables missing the column 4, and five tables missing the column 9.

Future tables entering the loop might miss columns as well. I do not have control on the source structure.

How can I keep using the function adding a null value for the missing columns so to maintain the structure defined in the RETURNS TABLE?

Upvotes: 0

Views: 92

Answers (1)

user330315
user330315

Reputation:

You can create a set returning function for this:

create function get_all_pages()
  returns table (....) 
as
$$
declare
  l_info_rec record;
begin
  for l_info_rec in select table_schema
                    from information_schema.tables
                    where table_name = 'page'
  loop
     return query 
        execute format('select %L as source_schema, * 
                       from %I.page', l_info_rec.table_schema, l_info_rec.table_schema);
  end loop;
end;
$$
language plpgsql;

Then run:

select *
from get_all_pages();

return query in a PL/pgSQL function doesn't end the function. It simply appends the result of the query to the result of the function.

You can pick any table as the return type, it just serves as a "placeholder" in this case (again: assuming all tables are 100% identical). Alternatively you could use returns table (....) - but that will require you to list all the columns of the table manually.

Note that this will buffer the complete result on the server before the function returns, so this might not be suitable for really large tables.

Another option is to create an event trigger that re-creates a VIEW (that does a UNION ALL) each time a new table is created or an existing one is dropped.

Upvotes: 1

Related Questions