Query on subquery that gets tables' names

Question

I have few tables in my database. They all have the same columns (id, name) but differ in the table name. Those tables have names that start with letter 'h'.
Not a very interesting schema design but I have to follow it.
I need to search for id in all those tables.

I tried something similar to:

select id from (select table_name 
FROM information_schema.tables 
where table_name like 'h%') as t;

I got error:

ERROR:  column "id" does not exist.

I understand the error now but I still do not know how to do the query?

Answer 1

You need dynamic SQL to do that since you cannot use values as identifiers in plain SQL. Write a PL/pgSQL function with EXECUTE:

CREATE FUNCTION f_all_tables()
  RETURNS TABLE (id int) AS
$func$
DECLARE
    _tbl regclass;
BEGIN

FOR _tbl IN
    SELECT c.oid::regclass
    FROM   pg_catalog.pg_class c
    JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE  c.relkind = 'r'
    AND    c.relname LIKE 'h%'
    AND    n.nspname = 'public' -- your schema name here
LOOP
    RETURN QUERY EXECUTE '
    SELECT id FROM ' || _tbl;
END LOOP;

END
$func$ LANGUAGE plpgsql;

I am using a variable of the object identifier type regclass to prevent SQL injection effectively. More about that in this related answer:
Table name as a PostgreSQL function parameter