Project

General

Profile

« Previous | Next » 

Revision 3049

Added by Aaron Marcuse-Kubitza about 12 years ago

sql.py: cast(): Use FOR loop with EXCEPTION block instead of CROSS JOIN with LEFT JOIN to insert entries into the errors table, to get the same optimization benefits this change provides in other filter-out contexts, and to improve clarity

View differences:

lib/sql.py
879 879
880 880 
    while True:
881 881 
        # Create function definition
882 
        errors_table_cols = map(sql_gen.Col,
883 
            ['column', 'value', 'error_code', 'error'])
882 884 
        query = '''\
883 885 
CREATE FUNCTION '''+function.to_str(db)+'''(value text)
884 886 
RETURNS '''+type_+'''
......
893 895 
EXCEPTION
894 896 
    WHEN data_exception THEN
895 897 
        -- Save error in errors table.
896 
        -- Insert the value and error for *each* source column.
897 
'''+mk_track_data_error(db, errors_table, srcs,
898 
    *map(sql_gen.CustomCode, ['value', 'SQLSTATE', 'SQLERRM']))+''';
898 
        DECLARE
899 
            error_code text := SQLSTATE;
900 
            error text := SQLERRM;
901 
            "column" text;
902 
        BEGIN
903 
            -- Insert the value and error for *each* source column.
904 
            FOR "column" IN
905 
'''+mk_select(db, sql_gen.NamedValues('c', None, [[c.name] for c in srcs]),
906 
    order_by=None)+'''
907 
            LOOP
908 
                BEGIN
909 
'''+mk_insert_select(db, errors_table, errors_table_cols,
910 
    sql_gen.Values(errors_table_cols).to_str(db))+''';
911 
                EXCEPTION
912 
                    WHEN unique_violation THEN NULL; -- continue to next row
913 
                END;
914 
            END LOOP;
915 
        END;
899 916
900 917 
        RAISE WARNING '%', SQLERRM;
901 918 
        RETURN NULL;

Also available in: Unified diff