Postgresql – To select from all tables inside the schema in PostgreSQL

postgresqlselect

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.

Best Answer

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.

Related Solutions

PostgreSQL: Using schema and table names from other tables

Jack has demonstrated the way to go. However, I feel there is room for improvement.

I place everything in schema x for convenient testing. Test setup:

DROP SCHEMA x CASCADE;
CREATE SCHEMA x;

-- meta tables for schema and table name    
CREATE TABLE x.schma(schma_id int, schma text);
INSERT INTO  x.schma VALUES (1, 'x');

CREATE TABLE x.tbl(tbl_id int, tbl text);
INSERT INTO  x.tbl VALUES (1, 't1'), (2, 't2');

-- dummy tables to be used in example query:
CREATE TABLE x.t1(id int);
INSERT INTO  x.t1 VALUES (1),(2);

CREATE TABLE x.t2(foo text);
INSERT INTO  x.t2 VALUES ('some text'), ('some more text');

Old function (original answer):

CREATE OR REPLACE FUNCTION x.f_dynaquery_old(int, int, _col text, _type anyelement, OUT col anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE '
   SELECT ' || quote_ident(_col) || '
   FROM   ' || (
       (SELECT schma FROM schma WHERE schma_id = $1) || '.' ||
       (SELECT tbl   FROM tbl   WHERE tbl_id   = $2))::regclass;
END
$func$  LANGUAGE plpgsql;

Cleaner version with format() (update 2017):

CREATE OR REPLACE FUNCTION x.f_dynaquery(_schma_id int, _tbl_id int
                                       , _col text, _type anyelement)
  RETURNS TABLE(col anyelement) AS
$func$
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT %I FROM %I.%I'
    , _col
    , (SELECT schma FROM schma WHERE schma_id = _schma_id)
    , (SELECT tbl   FROM tbl   WHERE tbl_id   = _tbl_id)
   );
END
$func$  LANGUAGE plpgsql;

COMMENT ON FUNCTION x.f_dynaquery(int, int, text, anyelement)
IS 'Query any column from a dynamically assembled tablename.
$1 .. id of schema
$2 .. id of table
$3 .. name of column
$4 .. type of column (only data type matters, not the value)';

Call:

SELECT col FROM x.f_dynaquery(1, 1, 'id', NULL::int);
  col
-----
   1
   2


SELECT col FROM x.f_dynaquery(1, 2, 'foo', NULL::text);
  col
----------------
 some text
 some more text

Major points

Function can return any column of any type. Read in the manual about Polymorphic Types and Declaring Function Parameters.
Defend against SQL injection by using quote_ident() and casting to regclass in the old version or with format() in the new one. Related:
- PL/pgSQL regclass quoting of table named like keyword
Retrieve the tablename by query as OP requested. Example is query by id but anything is possible.
Name the returned column, so it is easier to reference.

Select * from (every table in an subselect)

This is not possible without using PL/SQL or a loop to generate the SQL in the program you're using to execute the SQL.

Best Answer

Related Solutions

PostgreSQL: Using schema and table names from other tables

Major points

Select * from (every table in an subselect)

Related Question