I am in the process of restructurizing and unifying a large database in PostgreSQL. One part of this is to split up some large tables into smaller ones according to their value in ‘key’. The function I have written for this works well and looks like this:
CREATE OR REPLACE FUNCTION split_tables (table_string varchar(100)) RETURNS void AS $$ DECLARE r_row record; BEGIN FOR r_row IN SELECT type FROM type_list LOOP EXECUTE 'CREATE TABLE public.id_' || table_string || '__' || r_row.type || ' AS (SELECT * FROM id_' || table_string || ' WHERE lower(id_' || table_string || '.key) = lower(''' || r_row.type || '''));'; END LOOP; END; $$ LANGUAGE plpsql;
So, this goes through all entries in type_list and creates tables for each type. To do this for all tables that need to be split I call this function with
SELECT tobesplit.id FROM tobesplit, LATERAL split_tables(tobseplit.id);
However, this creates tables for ALL entries in type_list, even if there are no entries in the table to be split for some types. So in the end, I create a whole bunch of empty tables that I don’t need. I looked for ways to only create tables if the result is not empty, and found a possible solution in the answer for: CREATE TABLE IF ONLY NOT EMPTY RESULT SET.
This works if I test it seperately, but I have no idea how to incorporate this into the exectue statement. I always get syntax errors as soon as I write an IF or CASE-clause into the EXECUTE statement.
I am thankful for any ideas 🙂
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
You could change
FOR r_row IN SELECT type FROM type_list
FOR r_row IN EXECUTE format( 'SELECT DISTINCT lower(key) AS type FROM %I', 'id_' || table_string )
I would suggest that you use list partitioning and lump all the keys that don’t exist anywhere into a single partition.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0