CODEWITHSUNDEEP
postgresql
Artie Leech
Artie Leech

Reputation: 387

Select ALL from many postgres tables

We have around 40 tables, all with similar, but not identical fields. Each table has created_by and created_at fields.

We're trying to query a specific set of tables, and return only the created_at fields from rows created_by = 'john'

So far, we've got

select created_at from results_tables

where <TABLE_NAME> in
(
   select table_name from information_schema.tables 
   where <some criteria to limit tables being searched>
)
and created_by = 'john'

The inner query works on its own, and lists the correct tables. We want the outer query to get created_at from ALL the tables in the inner query.

So from the following tables:

table1
created_by   created_at val
john         12:00      5
sue          13:20      7

table20
created_by   created_at val
john         21:30      3
jane         23:45      21

we'd get:

12:00
21:30

Upvotes: 0

Views: 233

Answers (2)

Sarah
Sarah

Reputation: 15

do $$
DECLARE
i record;
xcreated_at text;

BEGIN

FOR i in  select table_name from information_schema.tables  LOOP

EXECUTE('select created_at from '||i.table_name||' where created_by = ''John''')INTO xcreated_at;
raise notice 'xcreated_at%',xcreated_at;
END LOOP;
END;

$$  ;

Upvotes: 0

matt525252
matt525252

Reputation: 732

You could create a function which will be accessing all your tables:

CREATE OR REPLACE FUNCTION some_schema.some_function_name()
returns TABLE(created_by varchar(300), created_at varchar(300))
AS $BODY$
DECLARE
  stmt text;
  BEGIN
    stmt = (
        WITH relevant_tables AS (
           SELECT table_name,
                  CONCAT('SELECT created_by::varchar(300), created_at::varchar(300) FROM ', table_name) as table_query
           FROM information_schema.tables 
           WHERE <some criteria to limit tables being searched>
        )
        SELECT string_agg(table_query, ' UNION ALL ') AS final_query
        FROM relevant_tables a
    );
    return query EXECUTE stmt;
  end; $BODY$
language plpgsql
;

I do not know what are your data types so I casted everything to varchar(300).

Having this function, you can then call:

SELECT * FROM some_schema.some_function_name()

You can also add any WHERE you like.

Upvotes: 1

Related Questions