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:

schemas/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 
$$;
schemas/vegbien.sql
185 185 
    LANGUAGE plpgsql IMMUTABLE
186 186 
    AS $$
187 187 
BEGIN
188 
    new.result := CAST(new.value AS growthform);
188 
    BEGIN
189 
        new.result := CAST(new.value AS growthform);
190 
    EXCEPTION
191 
        WHEN data_exception THEN
192 
            new.result := NULL;
193 
            RAISE WARNING '%', SQLERRM;
194 
    END;
189 195 
    RETURN new;
190 196 
END;
191 197 
$$;
......
199 205 
    LANGUAGE plpgsql IMMUTABLE
200 206 
    AS $$
201 207 
BEGIN
202 
    new.result := CAST(new.value AS placerank);
208 
    BEGIN
209 
        new.result := CAST(new.value AS placerank);
210 
    EXCEPTION
211 
        WHEN data_exception THEN
212 
            new.result := NULL;
213 
            RAISE WARNING '%', SQLERRM;
214 
    END;
203 215 
    RETURN new;
204 216 
END;
205 217 
$$;
......
213 225 
    LANGUAGE plpgsql IMMUTABLE
214 226 
    AS $$
215 227 
BEGIN
216 
    new.result := CAST(new.value AS taxonrank);
228 
    BEGIN
229 
        new.result := CAST(new.value AS taxonrank);
230 
    EXCEPTION
231 
        WHEN data_exception THEN
232 
            new.result := NULL;
233 
            RAISE WARNING '%', SQLERRM;
234 
    END;
217 235 
    RETURN new;
218 236 
END;
219 237 
$$;

Also available in: Unified diff