Question 88853 in PostgreSQL Question posted in
The official documentation can be found here.

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

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

enter image description here

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:

enter image description here

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.

Tags:

2

Answers


  1. 0

    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 $$;
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search