All we need is an easy explanation of the problem, so here it is.
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 🙂
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂