Skip to content

Instantly share code, notes, and snippets.

@ORESoftware
Created January 4, 2021 04:57
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ORESoftware/9ae683fa2e96f64934b73bf54cf837db to your computer and use it in GitHub Desktop.
Save ORESoftware/9ae683fa2e96f64934b73bf54cf837db to your computer and use it in GitHub Desktop.
how to create table partitions programmatically (with a loop)

Loop to create partitions does not work b/c a "CREATE TABLE" statement cannot be a prepared statement (laaaame).

do $$
declare
counter integer := 0;
begin
while counter <= 500 loop
    PREPARE create_table(int) AS
    CREATE TABLE mbk_auth_method_$1 PARTITION OF mbk_auth_method FOR VALUES WITH (modulus 500, remainder $1);
    EXECUTE create_table (counter);
    counter := counter + 1;
end loop;
end$$;

the above will yield a syntax error:

 syntax error at or near "CREATE"
LINE 7:     CREATE TABLE mbk_auth_method_$1 PARTITION OF mbk_auth_me...
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment