1 -- remove previous version of this function
2 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
4 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
6 attempt_value ALIAS FOR $1;
9 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
12 WHEN OTHERS THEN RETURN NULL;
14 $$ LANGUAGE PLPGSQL STRICT STABLE;
16 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
18 attempt_value ALIAS FOR $1;
19 fail_value ALIAS FOR $2;
23 EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
30 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
35 $$ LANGUAGE PLPGSQL STRICT STABLE;
37 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
39 attempt_value ALIAS FOR $1;
40 fail_value ALIAS FOR $2;
44 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
51 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
56 $$ LANGUAGE PLPGSQL STRICT STABLE;
58 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
60 attempt_value ALIAS FOR $1;
61 fail_value ALIAS FOR $2;
65 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
72 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
77 $$ LANGUAGE PLPGSQL STRICT STABLE;
79 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
81 attempt_value ALIAS FOR $1;
82 fail_value ALIAS FOR $2;
86 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
93 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
98 $$ LANGUAGE PLPGSQL STRICT STABLE;
100 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
102 attempt_value ALIAS FOR $1;
103 fail_value ALIAS FOR $2;
106 IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
107 RAISE EXCEPTION 'too many digits';
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;'
117 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
122 $$ LANGUAGE PLPGSQL STRICT STABLE;
124 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
126 attempt_value ALIAS FOR $1;
127 fail_value ALIAS FOR $2;
130 IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
131 RAISE EXCEPTION 'too many digits';
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;'
141 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
146 $$ LANGUAGE PLPGSQL STRICT STABLE;