sql/base/08-casting-and-validation.sql

   1 -- remove previous version of this function
   2 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
   3
   4 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
   5     DECLARE
   6         attempt_value ALIAS FOR $1;
   7         datatype ALIAS FOR $2;
   8     BEGIN
   9         EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
  10         RETURN attempt_value;
  11     EXCEPTION
  12         WHEN OTHERS THEN RETURN NULL;
  13     END;
  14 $$ LANGUAGE PLPGSQL STRICT STABLE;
  15
  16 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
  17     DECLARE
  18         attempt_value ALIAS FOR $1;
  19         fail_value ALIAS FOR $2;
  20         output DATE;
  21     BEGIN
  22         FOR output IN
  23             EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
  24         LOOP
  25             RETURN output;
  26         END LOOP;
  27     EXCEPTION
  28         WHEN OTHERS THEN
  29             FOR output IN
  30                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
  31             LOOP
  32                 RETURN output;
  33             END LOOP;
  34     END;
  35 $$ LANGUAGE PLPGSQL STRICT STABLE;
  36
  37 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
  38     DECLARE
  39         attempt_value ALIAS FOR $1;
  40         fail_value ALIAS FOR $2;
  41         output TIMESTAMPTZ;
  42     BEGIN
  43         FOR output IN
  44             EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
  45         LOOP
  46             RETURN output;
  47         END LOOP;
  48     EXCEPTION
  49         WHEN OTHERS THEN
  50             FOR output IN
  51                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
  52             LOOP
  53                 RETURN output;
  54             END LOOP;
  55     END;
  56 $$ LANGUAGE PLPGSQL STRICT STABLE;
  57
  58 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
  59     DECLARE
  60         attempt_value ALIAS FOR $1;
  61         fail_value ALIAS FOR $2;
  62         output NUMERIC(8,2);
  63     BEGIN
  64         FOR output IN
  65             EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
  66         LOOP
  67             RETURN output;
  68         END LOOP;
  69     EXCEPTION
  70         WHEN OTHERS THEN
  71             FOR output IN
  72                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
  73             LOOP
  74                 RETURN output;
  75             END LOOP;
  76     END;
  77 $$ LANGUAGE PLPGSQL STRICT STABLE;
  78
  79 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
  80     DECLARE
  81         attempt_value ALIAS FOR $1;
  82         fail_value ALIAS FOR $2;
  83         output NUMERIC(6,2);
  84     BEGIN
  85         FOR output IN
  86             EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
  87         LOOP
  88             RETURN output;
  89         END LOOP;
  90     EXCEPTION
  91         WHEN OTHERS THEN
  92             FOR output IN
  93                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
  94             LOOP
  95                 RETURN output;
  96             END LOOP;
  97     END;
  98 $$ LANGUAGE PLPGSQL STRICT STABLE;
  99
 100 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
 101     DECLARE
 102         attempt_value ALIAS FOR $1;
 103         fail_value ALIAS FOR $2;
 104         output NUMERIC(8,2);
 105     BEGIN
 106         IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
 107             RAISE EXCEPTION 'too many digits';
 108         END IF;
 109         FOR output IN
 110             EXECUTE 'SELECT ' || quote_literal((left(lpad(regexp_replace(attempt_value,'^0+',''),10,'0'),-2) || '.' || right(lpad(regexp_replace(attempt_value,'^0+',''),10,'0'),2))::numeric(8,2)) || '::NUMERIC(8,2) AS a;'
 111         LOOP
 112             RETURN output;
 113         END LOOP;
 114     EXCEPTION
 115         WHEN OTHERS THEN
 116             FOR output IN
 117                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
 118             LOOP
 119                 RETURN output;
 120             END LOOP;
 121     END;
 122 $$ LANGUAGE PLPGSQL STRICT STABLE;
 123
 124 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
 125     DECLARE
 126         attempt_value ALIAS FOR $1;
 127         fail_value ALIAS FOR $2;
 128         output NUMERIC(6,2);
 129     BEGIN
 130         IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
 131             RAISE EXCEPTION 'too many digits';
 132         END IF;
 133         FOR output IN
 134             EXECUTE 'SELECT ' || quote_literal((left(lpad(regexp_replace(attempt_value,'^0+',''),8,'0'),-2) || '.' || right(lpad(regexp_replace(attempt_value,'^0+',''),8,'0'),2))::numeric(6,2)) || '::NUMERIC(6,2) AS a;'
 135         LOOP
 136             RETURN output;
 137         END LOOP;
 138     EXCEPTION
 139         WHEN OTHERS THEN
 140             FOR output IN
 141                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
 142             LOOP
 143                 RETURN output;
 144             END LOOP;
 145     END;
 146 $$ LANGUAGE PLPGSQL STRICT STABLE;
 147