How to pass string from information_schema.tables as a table name for a second query

Question

i have 3 postgreSQL tables each with different dates stored in schema cm_nokia.

I want to create a view with the content of the table having the most recent date . I used the following query to get me the name of the most recent table

SELECT 
    table_name
    FROM (
        SELECT table_name,
               REGEXP_REPLACE(table_name, '\D', '', 'g') AS table_date
        FROM information_schema.tables
        WHERE table_name LIKE 'umts_cells_%'
    ) AS t
    WHERE table_name = (SELECT table_name FROM (SELECT table_name,
            REGEXP_REPLACE(table_name, '\D', '', 'g') AS table_date
            FROM information_schema.tables
            WHERE table_name LIKE 'umts_cells_%') t2
        ORDER BY table_date DESC
        LIMIT 1)

and it returned me correct:

i want to dynamically use the result of this query which is "umts_cells_20230427" to put it in a SELECT query :SELECT * FROM cm_nokia.umts_cells_20230427 but i don't know how to pass the string as a table name in this query.

score 0 · Answer 1 · answered Apr 27 '23 at 14:26

You can use a dynamic query to build the view, which has to run inside a function or inside an anonymous block:

DO $$
BEGIN

    EXECUTE format('CREATE OR REPLACE VIEW view_latest AS SELECT * FROM %s ', table_name)
    FROM  (
        SELECT 
        table_name
        FROM (
            SELECT table_name,
                   REGEXP_REPLACE(table_name, '\D', '', 'g') AS table_date
            FROM information_schema.tables
            WHERE table_name LIKE 'umts_cells_%'
        ) AS t
        WHERE table_name = (SELECT table_name FROM (SELECT table_name,
                REGEXP_REPLACE(table_name, '\D', '', 'g') AS table_date
                FROM information_schema.tables
                WHERE table_name LIKE 'umts_cells_%') t2
            ORDER BY table_date DESC
            LIMIT 1) )sub;
END $$;

score 0 · Answer 2 · answered Apr 27 '23 at 17:29

First of all you should take some case to prevent SQL Injection while passing table names as strings.

The proposal below uses regclass parameter to prevent unwanted side effects in a function that gets as a parameter the table name and returns the cursor of the table data.

I assume that all your tables have a identical structure, so simple choose one of them as the RETURNS rettype (in the example RETURNS setof t1)

CREATE OR REPLACE FUNCTION exec_query(tbl_name regclass)
RETURNS setof t1 
LANGUAGE 'plpgsql'
AS $$
BEGIN 
    RETURN QUERY  EXECUTE format('SELECT * FROM %I', tbl_name); 
END 
$$;

Simplified use case for tables t1 and t2

select * from exec_query('t1');
select * from exec_query('t2');

Usage with a query (again simplified)

select * from exec_query((select 't1'));
select * from exec_query((select 't2')) where a = 2;

See this answer more detailed discussion about the table name parameter

How to pass string from information_schema.tables as a table name for a second query

2 Answers2