Project

General

Profile

« Previous | Next » 

Revision 2412

Added by Aaron Marcuse-Kubitza about 12 years ago

schemas/functions.sql, vegbien.sql: Changed CAST-related relational functions to return NULL on data exceptions and convert the exceptions to warnings. This helps column-based import by mapping invalid values to NULL instead of aborting the whole query on the first invalid value.

View differences:

functions.sql
26 26 
    LANGUAGE plpgsql IMMUTABLE
27 27 
    AS $$
28 28 
BEGIN
29 
    new.result := CAST(new.value AS boolean);
29 
    BEGIN
30 
        new.result := CAST(new.value AS boolean);
31 
    EXCEPTION
32 
        WHEN data_exception THEN
33 
            new.result := NULL;
34 
            RAISE WARNING '%', SQLERRM;
35 
    END;
30 36 
    RETURN new;
31 37 
END;
32 38 
$$;
......
40 46 
    LANGUAGE plpgsql IMMUTABLE
41 47 
    AS $$
42 48 
BEGIN
43 
    new.result := CAST(new.value AS double precision);
49 
    BEGIN
50 
        new.result := CAST(new.value AS double precision);
51 
    EXCEPTION
52 
        WHEN data_exception THEN
53 
            new.result := NULL;
54 
            RAISE WARNING '%', SQLERRM;
55 
    END;
44 56 
    RETURN new;
45 57 
END;
46 58 
$$;
......
54 66 
    LANGUAGE plpgsql IMMUTABLE
55 67 
    AS $$
56 68 
BEGIN
57 
    new.result := CAST(new.value AS timestamp with time zone);
69 
    BEGIN
70 
        new.result := CAST(new.value AS timestamp with time zone);
71 
    EXCEPTION
72 
        WHEN data_exception THEN
73 
            new.result := NULL;
74 
            RAISE WARNING '%', SQLERRM;
75 
    END;
58 76 
    RETURN new;
59 77 
END;
60 78 
$$;

Also available in: Unified diff