CODEWITHSUNDEEP
postgresql
polarick
polarick

Reputation: 23

Query from a variable table in postgres

In my postgres DB, I Have some tables which their names vary based on the current date.

To be more specific their names are like these:

STATSDATA15_8_2019
STATSDATA14_8_2019
STATSDATA13_8_2019
...

I need an automated query that considering the current date, grabs data from the related table.

For example:

If the date is August 15th, I need: 

select * from STATSDATA15_8_2019

And if it is August 14th, I need: 

select * from STATSDATA14_8_2019

In fact i need an automated, dynamic query with variables.

Upvotes: 2

Views: 241

Answers (1)

S-Man
S-Man

Reputation: 23676

Disclaimer: I really would recommend to follow the advice of @a_horse_with_no_name creating a partitioned table!

In case that you would stay with your design, you could use a function which dynamically creates the query out of a format string.

This, of course, only works if all of your tables have the same structure.

demo:db<>fiddle

CREATE OR REPLACE FUNCTION select_table_by_date(the_date date) RETURNS TABLE(content text)
LANGUAGE plpgsql
AS $$
    BEGIN
        RETURN QUERY 
             EXECUTE format(
                 'SELECT * FROM statsdata%s', 
                 to_char(the_date, 'DD_FMMM_YYYY')
             );
    END;
$$;

SELECT * FROM select_table_by_date('2019-08-15'::date);

SELECT * FROM select_table_by_date(CURRENT_DATE);

Upvotes: 1

Related Questions