Automating partitions creation in Postgres 10

Question

I am trying to automate partitions in Postgres 10 in a huge table by BY RANGE (date_created).

I've noticed that there is no an automatic creation of partitioned tables, therefore I want to write a procedure to automate the creation of those tables.

I was thinking something like that:

CREATE OR REPLACE FUNCTION cdi.automating_partitions()
RETURNS TABLE(natural_id text, name text, natural_id_numeric text) AS
$func$
DECLARE
   formal_table text;
BEGIN
   FOR formal_table IN
       select '2017-01-01'::date + (n || ' months')::interval months,
       '2013-02-01'::date + (n || ' months')::interval monthsplus
       from generate_series(0, 12) n
   LOOP
      RETURN QUERY EXECUTE
   'CREATE TABLE cdi.' || 'document' || to_char(months, 'YYYY')  || ''  || to_char(months, 'MM') || ' PARTITION OF cdi.document
 FOR VALUES FROM  (''' ||  to_char(months, 'YYYY')  || to_char(months, 'MM')  || ''',
''' to_char(monthsplus, 'YYYY')  || to_char(monthsplus, 'MM')   ''');'
   END LOOP;
END
$func$  LANGUAGE plpgsql;

But I get a syntax error near (

You can't use execute outside of a PL/pgSQL block (and never as part of a SQL query) — user330315
– user330315, Commented Oct 17, 2017 at 10:26

klin · Accepted Answer · 2017-10-17 11:37:50Z

6

Use the function format() in conjunction with execute to get a clear and readable code, example:

do $do$
declare
    d date;
begin
    for d in
        select generate_series(date '2017-01-01', date '2017-12-01', interval '1 month')
    loop
    execute format($f$
        create table cdi.document%s%s partition of cdi.document
        for values from (%L) to (%L)
        $f$, 
        to_char(d, 'YYYY'), to_char(d, 'MM'), d, d+ interval '1 month');
    end loop;
end 
$do$

I have used an anonymous code block as create table ... does not generate any results. However, if you want to write a function, note that the function should return void and not use RETURN QUERY.

edited Oct 17, 2017 at 11:37

answered Oct 17, 2017 at 11:06

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

Automating partitions creation in Postgres 10

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related