SQL Insert Function with Input Validation

Objective

Develop a professional-grade SQL function to insert data into a table. The function will include input validation and will be accompanied by inline comments, necessary documentation, and an example to demonstrate its usage.

SQL Code

-- Function to insert data into a specified table with input validation
CREATE OR REPLACE FUNCTION insert_data(
    table_name TEXT,
    column_names TEXT[],
    column_values TEXT[]
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
    -- Input Validation
    IF table_name IS NULL OR table_name = '' THEN
        RAISE EXCEPTION 'Table name cannot be null or empty';
    END IF;
    
    IF array_length(column_names, 1) IS NULL THEN
        RAISE EXCEPTION 'Column names array cannot be null or empty';
    END IF;
    
    IF array_length(column_values, 1) IS NULL THEN
        RAISE EXCEPTION 'Column values array cannot be null or empty';
    END IF;
    
    IF array_length(column_names, 1) <> array_length(column_values, 1) THEN
        RAISE EXCEPTION 'Column names and values arrays must have the same length';
    END IF;

    -- Dynamic SQL for data insertion
    EXECUTE format(
        'INSERT INTO %I (%s) VALUES (%s)', 
        table_name, 
        array_to_string(column_names, ', '), 
        array_to_string(column_values, ', ')
    );
END;
$$;

Documentation Block

/*
Function: insert_data
Purpose: Insert data into a specified table with input validation.
Parameters:
    - table_name: TEXT - The name of the table into which data is to be inserted.
    - column_names: TEXT[] - An array of column names.
    - column_values: TEXT[] - An array of column values corresponding to the column names.
Returns: VOID
Exceptions: Raises exceptions for null or empty table name, null or empty column name/value arrays, and mismatched array lengths.
*/

Inline Comments Explanation

1. Input Validation:

   • Check if the table name is null or empty and raise an exception if true.
   • Check if the column names array is null or empty and raise an exception if true.
   • Check if the column values array is null or empty and raise an exception if true.
   • Ensure the column names array and column values array have the same length to avoid mismatched input.

2. Dynamic SQL Execution:

   • Use the format function to dynamically construct the SQL query.
   • Insert the data into the specified table using dynamic SQL.

Example Usage

-- Example table creation
CREATE TABLE example_table (
    id SERIAL PRIMARY KEY,
    name TEXT,
    age INT
);

-- Using the insert_data function to insert a row into the example_table
SELECT insert_data(
    'example_table', 
    ARRAY['name', 'age'], 
    ARRAY['''John Doe''', '30']
);

-- Verifying the inserted data
SELECT * FROM example_table;

In the example provided:

• A table example_table with columns id, name, and age is created.
• The function insert_data is used to insert a row into example_table.
• Finally, a SELECT query is used to verify the inserted data.

This function provides a robust and scalable way to insert data with proper validation in SQL, adhering to best practices in software engineering. For more advanced analytics and data manipulation skills, consider exploring courses on the Enterprise DNA platform.