Revision 2949
Added by Aaron Marcuse-Kubitza over 12 years ago
| schemas/functions.sql | ||
|---|---|---|
| 140 | 140 |
LANGUAGE plpgsql IMMUTABLE |
| 141 | 141 |
AS $$ |
| 142 | 142 |
BEGIN |
| 143 |
IF new.type = 'str' THEN -- no cast needed |
|
| 144 |
new.result := (SELECT nullif(new.value, new."null")); |
|
| 143 |
new.result := functions."_nullIf"(new.value, new."null", new.type); |
|
| 144 |
RETURN new; |
|
| 145 |
END; |
|
| 146 |
$$; |
|
| 147 |
|
|
| 148 |
|
|
| 149 |
-- |
|
| 150 |
-- Name: _nullIf(text, text, datatype); Type: FUNCTION; Schema: functions; Owner: - |
|
| 151 |
-- |
|
| 152 |
|
|
| 153 |
CREATE FUNCTION "_nullIf"(value text, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS text |
|
| 154 |
LANGUAGE plpgsql IMMUTABLE |
|
| 155 |
AS $$ |
|
| 156 |
DECLARE |
|
| 157 |
"null" text NOT NULL := "null"; -- add NOT NULL |
|
| 158 |
type functions.datatype NOT NULL := type; -- add NOT NULL |
|
| 159 |
BEGIN |
|
| 160 |
IF type = 'str' THEN RETURN nullif(value, "null"); -- no cast needed |
|
| 145 | 161 |
-- Invalid value is ignored, but invalid null value generates error |
| 146 |
ELSIF new.type = 'float' THEN
|
|
| 162 |
ELSIF type = 'float' THEN |
|
| 147 | 163 |
DECLARE |
| 148 | 164 |
-- Outside the try block so that invalid null value generates error |
| 149 |
"null" double precision := new."null"::double precision;
|
|
| 165 |
"null" double precision := "null"::double precision; |
|
| 150 | 166 |
BEGIN |
| 151 |
new.result := (SELECT nullif(new.value::double precision, "null"));
|
|
| 167 |
RETURN nullif(value::double precision, "null");
|
|
| 152 | 168 |
EXCEPTION |
| 153 |
WHEN data_exception THEN NULL; -- ignore invalid value
|
|
| 169 |
WHEN data_exception THEN RETURN value; -- ignore invalid value
|
|
| 154 | 170 |
END; |
| 155 | 171 |
END IF; |
| 156 |
|
|
| 157 |
RETURN new; |
|
| 158 | 172 |
END; |
| 159 | 173 |
$$; |
| 160 | 174 |
|
Also available in: Unified diff
schemas/functions.sql: Added plain function _nullIf() and use it in trigger function _nullIf()