I would like to automatically create a table, in my postgresql db, every month using stored data. Every time the query runs I would like the table name to change dynamically (e.g table_JAN2018, table_FEB2018, table_MAR2018 etc) so as a new table is created with the new data of that month while the old tables remain intact.
After researching, I found that I can achieve this (?) as described in the link below: Table name as a PostgreSQL function parameter
So I tried, in same fashion, the code below:
CREATE FUNCTION table_Jan2018() RETURNS void as
$func$
BEGIN
EXECUTE
'create table' || to_char(current_date, '%b/%Y'|| 'as select * from
(
select
id,product, client, purchase_date, sales_status,
case when complete_transaction_date is null then cast(purchase_date as date)
when verified is not null then cast(complete_transaction_date as date)
end as purchase
from clients
where sales_status in ('state1','state2')
and test=false
)
where purchase>=(current_date - interval '30' day)';
end;
However, what i get is the following error message in presto: "no viable alternative at input 'CREATE FUNCTION' "
Is the above function suitable in order to change dynamically the table name as I need? Is there another way to achieve what i want?
Thank you in advance!!
