Dear all,

I am an accidental postgres DBA and learning things every day. Apologies
for my questions if not properly drafted.

I am trying to load data from the temp table to the main table and catch
the exceptions inside another table.

temp table is cast with the main table data type and trying to load the
data.

temp table is below.

    category_name                          |            description
             | is_active
-------------------------------------------+---------------------------------------------+-----------
 *Tech123212312312323233213123123123123    *| Furniture and home decor
               | true
 *Tech123212312312323233213123123123123    *| Electronic devices and
accessories          | true
 Elec                                    | Books of various genres
            | *15*
 TV                                      | Books
            | *12*
 cla                                     | Apparel and fashion accessories
            | true

category name is varchar(25) and is_active is boolean in main table. So i
should get exceptions for 1st,2nd for category_name rows and 4 and 5th rows
for boolean. In exception table results,its only showing

Exception table is below. Here instead of showing exception for value 12 in
the is_active table its showing old exception for 15 itself.. Script is
attached,,...SQLERRM value is not getting updated for row 12..WHat could be
the reason for this?

value too long for type character varying(25) category_name 1 2024-01-16
16:17:01.279 +0530 value too long for type character varying(25)
description 2 2024-01-16 16:17:01.279 +0530 invalid input syntax for type
boolean: "15" is_active 3 2024-01-16 16:17:01.279 +0530 *invalid input
syntax for type boolean: "15" * 4 2024-01-16 16:17:01.279 +0530 *invalid
input syntax for type boolean: "15"* 5 2024-01-16 16:17:01.279 +0530
CREATE OR REPLACE FUNCTION insert_temp_data_to_main_table()
RETURNS VOID AS $$
DECLARE
    v_main_table_name TEXT := 'main_categories';
    v_temp_table_name TEXT := 'tmp_categories';
    v_error_table_name TEXT := 'error_log_table';
    v_sql_statement TEXT;
BEGIN
    -- Clear the error log table
    EXECUTE 'TRUNCATE TABLE ' || v_error_table_name;

    -- Build the complete SQL statement with aggregated columns and select 
clauses
    v_sql_statement := format('
        INSERT INTO %I (%s)
        SELECT %s
        FROM %I',
        v_main_table_name,
        (SELECT string_agg(column_name, ', ') FROM information_schema.columns 
WHERE table_name = v_main_table_name),
        (SELECT string_agg('CAST(' || v_temp_table_name || '.' || column_name 
|| ' AS ' || data_type || ')', ', ') FROM information_schema.columns WHERE 
table_name = v_temp_table_name),
        v_temp_table_name);

    -- Print the SQL statement
    RAISE NOTICE 'Generated SQL statement: %', v_sql_statement;

    -- Insert data into the main table from the temp table
    EXECUTE v_sql_statement;

EXCEPTION
    WHEN others THEN
        DECLARE
            v_error_msg TEXT;
            v_failed_column_name TEXT;
            v_row_counter INT := 1;
        BEGIN
            -- Get the specific error message
            v_error_msg := SQLERRM;

            -- Get the failed column name
            SELECT column_name INTO v_failed_column_name
            FROM information_schema.columns
            WHERE table_name = v_temp_table_name
            ORDER BY ordinal_position
            LIMIT 1 OFFSET v_row_counter - 1;

            -- Log the error into the error log table
            EXECUTE format('
                INSERT INTO %I (error_message, failed_column_name, 
failed_row_number)
                VALUES ($1, $2, $3)', v_error_table_name)
            USING v_error_msg, v_failed_column_name, v_row_counter;
        END;
END;
$$ LANGUAGE plpgsql;

Reply via email to