In PostgreSQL I have tables to store many millions of rows, with over 90 columns. I have under 100 of these, but when I add or adjust a column in the template “d_template” table, I have to manually change them all-and they are growing. The working tables all have a prefix of “d_”, which has it’s own challenges, as the underscore character “_” is seen on some platforms as a variable/wildcard. Add to this that SQL treats a “_” differently than pl/pgsql… I’m using JDBC/Postgresql.

I wanted to run a single function with the template table hard-coded, and have it do the following:

 

CREATE OR REPLACE FUNCTION raw.f_update_raw_d_template_with_data_source() RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
source_schema CONSTANT text := 'data';
source_table CONSTANT text := 'd_template';
target_schema CONSTANT text := 'raw';
target_prefix CONSTANT text := 'd_';
source_columns RECORD;
target_column RECORD;
column_type text;


BEGIN
-- Loop through each column in the source template table
FOR source_columns IN
SELECT column_name, data_type, is_nullable, character_maximum_length
FROM information_schema.columns
WHERE table_schema = source_schema
AND table_name = source_table
LOOP
-- Loop through each target table that starts with the specified prefix
FOR target_column IN
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = target_schema
AND table_name LIKE target_prefix || '%' ESCAPE '\'
AND column_name = source_columns.column_name
LOOP
-- If column exists, check if it matches the type and nullable constraints
IF target_column.data_type != source_columns.data_type OR 
(target_column.is_nullable = 'YES' AND source_columns.is_nullable = 'NO') THEN

column_type := source_columns.data_type;



-- Adjust character length if applicable
IF source_columns.character_maximum_length IS NOT NULL THEN
column_type := column_type || '(' || source_columns.character_maximum_length || ')';
END IF;



EXECUTE format(
'ALTER TABLE %I.%I ALTER COLUMN %I TYPE %s',
target_schema,
target_prefix || '%',
source_columns.column_name,
column_type
);



-- Adjust nullable constraint
IF source_columns.is_nullable = 'NO' THEN
EXECUTE format(
'ALTER TABLE %I.%I ALTER COLUMN %I SET NOT NULL',
target_schema,
target_prefix || '%',
source_columns.column_name
);
ELSE
EXECUTE format(
'ALTER TABLE %I.%I ALTER COLUMN %I DROP NOT NULL',
target_schema,
target_prefix || '%',
source_columns.column_name
);
END IF;



END IF;
END LOOP;



-- If column does not exist in the target table, add it
IF NOT FOUND THEN
column_type := source_columns.data_type;



-- If character_maximum_length is specified, add that to column_type
IF source_columns.character_maximum_length IS NOT NULL THEN
column_type := column_type || '(' || source_columns.character_maximum_length || ')';
END IF;



EXECUTE format(
'ALTER TABLE %I.%I ADD COLUMN %I %s %s',
target_schema,
target_prefix || '%',
source_columns.column_name,
column_type,
CASE WHEN source_columns.is_nullable = 'NO' THEN 'NOT NULL' ELSE '' END
);
END IF;
END LOOP;



-- Remove any columns from the target table that are not in the source table
FOR target_column IN
SELECT column_name
FROM information_schema.columns
WHERE table_schema = target_schema
AND table_name LIKE target_prefix || '%' ESCAPE '\'
AND column_name NOT IN (
SELECT column_name
FROM information_schema.columns
WHERE table_schema = source_schema
AND table_name = source_table
)
LOOP
EXECUTE format(
'ALTER TABLE %I.%I DROP COLUMN %I',
target_schema,
target_prefix || '%',
target_column.column_name
);
END LOOP;

END $$;