procedure that takes the distinct tables with incremented (int) key and sub the int back for the original value

1 vote
0 answers
23 views
postgresql stored-procedures design-pattern
                          I'm working on developing a set of stored procedures in PostgreSQL to abstract common operations I'm seeking advice on implementing a pattern for substituting values for INTS in child tables. 

Please let me know if you have any questions. 

Below is the main routine I've designed so far. Pseudocode is fine; 

    CREATE OR REPLACE PROCEDURE your_main_routine(
        p_full_column_name VARCHAR,
        p_drop_original_column BOOLEAN DEFAULT FALSE,
        p_set_pk BOOLEAN DEFAULT FALSE
    ) AS
    $$
    DECLARE
        -- Declare variables
        p_db_name           VARCHAR;
        p_schema_name       VARCHAR;
        p_table_name        VARCHAR;
        p_column_name       VARCHAR;
        p_temp_table_buffer TEXT;
        run_loop            BOOLEAN := TRUE; -- Initialize run_loop variable
    BEGIN
    
        -- logging to trace script execution
        RAISE NOTICE 'Starting script execution with parameters: p_full_column_name: %, p_drop_original_column: %, p_set_pk: %',
            p_full_column_name, p_drop_original_column, p_set_pk;
    
        -- Parse the database, schema, table, and column names from the input string
        RAISE NOTICE 'Parsing column names from input string with parameter: p_full_column_name: %', p_full_column_name;
        p_db_name := split_part(p_full_column_name, '.', 1);
        RAISE NOTICE 'Parsed database name: %', p_db_name;
    
        p_schema_name := split_part(p_full_column_name, '.', 2);
        RAISE NOTICE 'Parsed schema name: %', p_schema_name;
    
        p_table_name := split_part(p_full_column_name, '.', 3);
        RAISE NOTICE 'Parsed table name: %', p_table_name;
    
        p_column_name := split_part(p_full_column_name, '.', 4);
        RAISE NOTICE 'Parsed column name: %', p_column_name;
    
        IF p_set_pk THEN
            -- Call the function to create the table with PK
            p_temp_table_buffer := 'temp_buffer';
            -- Adjust this accordingly
    
            -- Loop until run_loop is set to FALSE
            WHILE run_loop
                LOOP
                    -- Call the create_distinct_table procedure with your parameters
                    CALL create_distinct_table(p_table_name, p_column_name, p_temp_table_buffer, run_loop);
    
                    -- Pause execution for 1 second
                    PERFORM pg_sleep(1);
    
                    -- Set run_loop to FALSE once the procedure is done
                    run_loop := FALSE;
                END LOOP;
    
    
            run_loop := true;
            -- Loop until run_loop is set to FALSE
            WHILE run_loop
                LOOP
                    -- Call the create_distinct_table procedure with your parameters
                    CALL add_int_column_to_table(p_temp_table_buffer, 'id', run_loop);
    
                    -- Pause execution for 1 second
    
                    PERFORM pg_sleep(1);
                    run_loop := FALSE;
    
                    -- Set run_loop to FALSE once the procedure is done
                END LOOP;
    
            run_loop := true;
            -- Loop until run_loop is set to FALSE
            WHILE run_loop
                LOOP
                    -- Call the create_distinct_table procedure with your parameters
                    CALL add_sequence_to_column(p_temp_table_buffer, 'id', run_loop);
    
                    -- Pause execution for 1 seconds
    
                    PERFORM pg_sleep(1);
                    run_loop := FALSE;
    
                    -- Set run_loop to FALSE once the procedure is done
                END LOOP;
    
            run_loop := true;
            -- Loop until run_loop is set to FALSE
            WHILE run_loop
                LOOP
                    -- Call the create_distinct_table procedure with your parameters
                    CALL add_sequence_to_column(p_temp_table_buffer, 'id', run_loop);
    
                    -- Pause execution for 1 seconds
    
                    PERFORM pg_sleep(1);
                    run_loop := FALSE;
    
                    -- Set run_loop to FALSE once the procedure is done
                END LOOP;
    
        END IF;
    END;
    $$ LANGUAGE plpgsql;
    
    
    -- Call your main routine
    CALL your_main_routine('postgres.some_schema.some_table.some_customer_id', FALSE, TRUE);


I am thinking along these lines (un tested) 
    
    CREATE OR REPLACE PROCEDURE add_foreign_key(
        p_constraint_name VARCHAR,
        p_table_name VARCHAR,
        p_column_name VARCHAR,
        p_referenced_table VARCHAR,
        p_referenced_column VARCHAR
    ) AS
    $$
    BEGIN
        -- Generate the SQL statement to add the foreign key constraint
        EXECUTE format('ALTER TABLE %I ADD CONSTRAINT %I FOREIGN KEY (%I) REFERENCES %I(%I)', 
                       p_table_name, p_constraint_name, p_column_name, 
                       p_referenced_table, p_referenced_column);
    END;
    $$ LANGUAGE plpgsql;

and this 

    CREATE OR REPLACE PROCEDURE add_primary_key(p_table_name VARCHAR, p_column_name VARCHAR) AS
    $$
    BEGIN
        -- Generate the SQL statement to add the primary key constraint
        EXECUTE format('ALTER TABLE %I ADD PRIMARY KEY (%I)', p_table_name, p_column_name);
    END;
    $$ LANGUAGE plpgsql;

I am just trying to make the procedure that takes the distinct tables with incremented (int) key and sub the int back for the original value, then hook the above procedures in. 
                        
Asked by RandomNumberFun (113 rep)
Mar 13, 2024, 05:02 AM
Last activity: Mar 13, 2024, 05:44 AM
procedure that takes the distinct tables with incremented (int) key and sub the int back for the original value

Related Questions
