To select from all tables inside the schema in PostgreSQL

Is it possible to select from all tables inside the schema?
I got all the table names from

select table_name from information_schema.tables

but I am unable to use it to do my query.

You can't do it directly as one can not write a 'normal' query if the table names are not known (ie. coming from some variable or subquery). But you can build and execute a dynamic SQL statement for this. For example, if you need the column 'name' from every table, you can do the following (inside a PL/pgSQL function):

FOR i IN SELECT table_name 
           FROM information_schema.tables 
          WHERE table_schema = 'your_desired_schema'
LOOP
    sql_string := sql_string || format($
        -- some whitespace is mandatory here
        UNION
        SELECT name FROM %I
    $,
    i.table_name);
END LOOP;

EXECUTE sql_string;

In this form it won't work however, since you cannot SELECT in plpqsql unless you do it into a variable. You can either create a temporary table for this, loop over the results (in an other FOR loop), or - not using UNION - just return in every iteration, depending on your needs.

And, of course, this presumes that you want to select a single column (or more than one, but always with the same name and type) from all the tables. If you simply need all the data from every table, then the tables must have the same structure (the same column types in the same order, with the same names).

Notes:

• the format() function was introduced in version 9.1

• I've omitted some mandatory elements of any PL/pgSQL block to keep it simple

• the earlier version mentioned using a DO block. The problem with it is that you cannot simply use SELECT there to return rows, as it was pointed out in another answer.

StackExchange Database Administrators Q#44729, answer score: 5