Query from a variable table in postgres

Question

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.

you should create a partitioned table instead.

user330315
– user330315

2019-08-15 11:49:29 +00:00
Commented Aug 15, 2019 at 11:49 — user330315
– user330315, Commented Aug 15, 2019 at 11:49

S-Man · Accepted Answer · 2019-08-15 12:56:05Z

1

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);

edited Aug 15, 2019 at 12:56

answered Aug 15, 2019 at 12:46

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

polarick Over a year ago

Dear S-Man Really appreciate your helpful answer. But i made a mistake about the tables' naming convention. They are actually like this: STATSDATA8_7_2019 , STATSDATA8_9_2019 , STATSDATA8_10_2019 , STATSDATA8_15_2019 , I'd be extremely grateful if you revise your answer based on this modification to solve my problem. tnx a lot

S-Man Over a year ago

Just change the date format string to 'FMMM-FMDD-YYYY'

Collectives™ on Stack Overflow

Query from a variable table in postgres

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related