1

Is there anybody who can help me with making a query with the following functionality:

  1. Let's have a simple statement like:

    SELECT relname FROM pg_catalog.pg_class WHERE relkind = 'r';

    This will produce a nice result with a single column - the names of all tables.

  2. Now lets imagine that one of the tables has name "table1". If we execute: 

    SELECT count(*) FROM table1;

    we will get the number of rows of the table "table1".

  3. Now the real question - how these two queries can be unified and to have one query, which to give the result of two columns: name of the table and number of rows? Written in pseudo SQL it should be something like this:

    SELECT relname, (SELECT count(*) FROM relname::[as table name]) FROM pg_catalog.pg_class WHERE relkind = 'r';

And here is and example - if there are 3 tables in the database and the names are table1, table2 and table 3, and they have respectively 20, 30 and 40 rows, the query result should be like this:

 -------------
|relname| rows|
|-------------|
|table1 |   20|
|-------------|
|table2 |   30|
|-------------|
|table3 |   40|
 -------------

Thanks to everyone who is willing to help ;-)

P.S. Yes I know that the table name is not schema-qualified ;-) Let's hope that all tables in the database have unique names ;-)

(Corrected typos from rename to relname in last query)

EDIT1: The question is not related to "how can I find the number of rows in a table". What I'm asking is: how to build a query with 2 selects and the second to have as FROM the value of a column from the result of the first select.

EDIT2: As @jdigital suggested I've tried the dynamic querying and it does the job, but can be used only in PL/pgSQL. So it doesn't fit my needs. In additional I tried with PREPARE and EXECUTE statement - yet again it is not working. Anyway - I'll stick with the two queries approach. But I'm damn sure that PostgreSQL is capable of this ....

Ivaylo Nikolov
  • 501
  • 4
  • 13
  • 2
    BTW, [How do you find the row count for all your tables in Postgres](http://stackoverflow.com/q/2596670/593144) – Abelisto Jun 19 '16 at 15:20
  • Is there a reason you can't use PL/pgSQL? – jdigital Jun 21 '16 at 04:24
  • I'm not an expert in PostgreSQL (I wish I am) but AFAIK one can not use PL/pgSQL in a statement (of course there may be function calls ... etc). But what I'm actually trying to do is to execute a query (SELECT) and based on the result to generate another query and to combine the result in one. Can you do this ??? It should be something like `SELECT relname, [PL/pgSQL BLOCK:: EXECUTE 'SELECT count(*) FROM ' || relname ] FROM pg_catalog.pg_class WHERE relkind = 'r';`. I don't even know how to pseudo-code it ... – Ivaylo Nikolov Jun 21 '16 at 07:40
  • One way to achieve this is with SELECT statements that are UNION'ed together. Your script (in any programming language you like, with PL/pgSQL as an easily available choice) would iterate over the tables, building a string with SELECTs and UNIONs for each table in the catalog. When you're done, you just execute it. The key is that you're generating an SQL statement as a string. You're not exercising any magical SQL powers: you first have to understand how to decompose this problem into one that can be solved with plain SQL (and once there, yougenerate it). – jdigital Jun 22 '16 at 02:57
  • So you're going to build a string like this: `"SELECT 'table1', count(*) from table1 UNION SELECT 'table2', count(*) from table2 ..."` – jdigital Jun 22 '16 at 03:02
  • skype: ivailon ...pls contact me if you have time. I don't want to disclose the case here ;-) – Ivaylo Nikolov Jun 23 '16 at 11:21

1 Answers1

1

With PL/pgSQL (postgres SQL Procedural Language), you can execute dynamic queries by building a string and then executing it as SQL. Note that this is postgres-specific, but other databases are likely to have something equivalent. Even more generally, if you are willing to go beyond SQL, you can do this with any programming language (or shell/cmd script).

By the way, you'll get better results searching for "postgres dynamic query" since "nested select" has a different meaning.

jdigital
  • 11,926
  • 4
  • 34
  • 51