CODEWITHSUNDEEP
postgresqlplpgsql
Randy Banks
Randy Banks

Reputation: 371

Create columns from the result of generate_series in Postgres

I feel like this should be very straightforward, but I cannot seem to find a solution.

I am trying to create a plpgsql function that takes as input a start date and an end date, that then generates a series of years between the two dates that I can use as columns for my new table.

For example, if I call 

my_function('2010-01-01', '2015-01-1')

I want a new table with columns 2010, 2011, 2012, 2013, 2014, and 2015.

I'm inclined to use the generate_series() function to do this, but I cannot figure out how to actually store the values it returns and pull them out to create my table with.

I'm using Postgres 9.5.3

Upvotes: 0

Views: 1944

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125564

select
    'create table t ("' || 
    string_agg (to_char(y,'YYYY'), '" text, "') ||
    '" text)'
from generate_series('2010-01-01'::date, '2015-01-1', '1 year') y(y)
;
                                           ?column?                                            
-----------------------------------------------------------------------------------------------
 create table t ("2010" text, "2011" text, "2012" text, "2013" text, "2014" text, "2015" text)
(1 row)

Upvotes: 0

klin
klin

Reputation: 121919

Use dynamic sql with execute format.

Assuming that the new table should have text columns:

create or replace function create_table(table_name text, from_year int, to_year int)
returns void language plpgsql as $$
begin
    execute format('create table %I(%s)', 
        table_name, 
        string_agg(concat('"', y::text, '" text'), ',')) 
    from generate_series(from_year, to_year) y;
end $$;

select create_table('my_table', 2010, 2015);

\d my_table

  Table "public.my_table"
 Column | Type | Modifiers 
--------+------+-----------
 2010   | text | 
 2011   | text | 
 2012   | text | 
 2013   | text | 
 2014   | text | 
 2015   | text |

Upvotes: 1

Related Questions