PostgreSQL. Функции. Контроль и преобразование значений

В процессе формирования…

Важно!
1. При копировании текстов функций этого раздела, следует «крупно иметь в виду», что существенная часть функций вызывает другие функции (размещенные в этом разделе, а также и в других разделах тоже).
2. Чтобы компенсировать этот момент — в конце страницы можно скачать исходные тексты всех функций в формате PG-backup (SQL-текст).

Проверка значения на NULL с заменой (если NULL) на заданное значение по умолчанию (функция COALESCE не достаточно строга в некоторых случаях):
- str_is_null — контроль значения типа TEXT (проверка на NULL и замена NULL на значение по умолчанию);
- str_is_null — контроль значения типа varchar (проверка на NULL и замена NULL на значение по умолчанию);
- int_is_null — контроль значения типа integer (проверка на NULL и замена NULL на значение по умочанию);
- bigint_is_null — контроль значения типа bigint (проверка на NULL и замена NULL на значение по умочанию);
- float_is_null — контроль значения типа double precision (проверка на NULL и замена NULL на значение по умочанию);
- bool_is_null — контроль значения типа boolean (проверка на NULL и замена NULL на значение по умочанию).
Проверка, что строковое значение можно интерпретировать, как значение заданного типа:
- its_bigint — проверка, что строка — это целое число (bigint);
- its_integer — проверка, что строка — это целое число (integer);
- its_float — проверка, что строка — это число (float);
- its_bool — проверка, что строка — это логическое значение (boolean).
Конвертировать значение заданного типа в строку:
- bool_as_str — конвертировать логическое значение в строку;
- bigint_as_str — конвертировать bigint в строку;
- int_as_str — конвертировать integer в строку;
- float_as_str — конвертировать double precision в строку;
Конвертировать строку в значение заданного типа:
- str_as_bool — конвертировать строку в boolean;
- str_as_bigint — конвертировать строку в bigint;
- str_as_int — конвертировать строку в integer;
- str_as_float — конвертировать строку в double precision;

CREATE OR REPLACE FUNCTION public.str_is_null(
    sx text,
    sdef text DEFAULT ''::text,
    yestrim boolean DEFAULT true)
  RETURNS text AS
$BODY$
DECLARE
  res text;
BEGIN
  -- Контроль значения типа TEXT (проверка на NULL и замена NULL на значение по умолчанию)
  /* 
     Примеры:
       select * from str_is_null('   123   ');
       select * from str_is_null('   123   ', '', false);
       select * from str_is_null(null);
       select * from str_is_null(null, '   567   ');
       select * from str_is_null(null, '   567   ', false);
  */
  res = sx;
  IF (res is NULL) THEN
     IF (sdef is NULL) THEN sdef=''; END IF;
     res = sdef;
  END IF;
  IF (res <> '') THEN
      yestrim = bool_is_null(yestrim);
      IF (yestrim = TRUE) THEN res = btrim(res); END IF;
  END IF;
  RETURN res;  
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.str_is_null(text, text, boolean) IS 'Контроль значения типа TEXT (проверка на NULL и замена NULL на значение по умолчанию)';

CREATE OR REPLACE FUNCTION public.str_is_null(
    sx character varying,
    sdef character varying DEFAULT ''::character varying,
    yestrim boolean DEFAULT true)
  RETURNS character varying AS
$BODY$
DECLARE
  res character varying;
BEGIN
  -- Контроль значения типа varchar (проверка на NULL и замена NULL на значение по умолчанию)
  /* 
     Примеры:
       select * from str_is_null('   123   ');
       select * from str_is_null('   123   ', '', false);
       select * from str_is_null(null);
       select * from str_is_null(null, '   567   ');
       select * from str_is_null(null, '   567   ', false);
  */
  res = sx;
  IF (res is NULL) THEN
     IF (sdef is NULL) THEN sdef=''; END IF;
     res = sdef;
  END IF;
  IF (res <> '') THEN
      yestrim = bool_is_null(yestrim);
      IF (yestrim = TRUE) THEN res = btrim(res); END IF;
  END IF;
  RETURN res;  
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.str_is_null(character varying, character varying, boolean) IS 'Контроль значения типа varchar (проверка на NULL и замена NULL на значение по умолчанию)';

CREATE OR REPLACE FUNCTION public.int_is_null(
    sx integer,
    sdef integer DEFAULT 0)
  RETURNS integer AS
$BODY$
BEGIN
  -- Контроль значения типа integer (проверка на NULL и замена NULL на значение по умочанию)
  /*
     select * from int_is_null(null, -5);
     select * from int_is_null(null);
     select * from int_is_null(0, -5);
  */
  IF (sx is NULL) THEN
      IF (sdef is NULL) THEN sdef=0; END IF;
      RETURN sdef;
  ELSE
    RETURN sx;
  END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.int_is_null(integer, integer) IS 'Контроль значения типа integer (проверка на NULL и замена NULL на значение по умочанию)';

CREATE OR REPLACE FUNCTION public.bigint_is_null(
    sx bigint,
    sdef bigint DEFAULT 0)
  RETURNS bigint AS
$BODY$
BEGIN
  -- Контроль значения типа bigint (проверка на NULL и замена NULL на значение по умочанию)
  /*
     select * from bigint_is_null(null, -5);
     select * from bigint_is_null(null);
     select * from bigint_is_null(0, -5);
  */
  IF (sx is NULL) THEN
      IF (sdef is NULL) THEN sdef=0; END IF;
      RETURN sdef;
  ELSE
    RETURN sx;
  END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.bigint_is_null(bigint, bigint) IS 'Контроль значения типа bigint (проверка на NULL и замена NULL на значение по умочанию)';

CREATE OR REPLACE FUNCTION public.float_is_null(
    sx double precision,
    sdef double precision DEFAULT 0.0)
  RETURNS double precision AS
$BODY$
BEGIN
  -- Контроль значения типа double precision (проверка на NULL и замена NULL на значение по умочанию)
  /*
   select * from public.float_is_null(5, 20); -- 5
   select * from public.float_is_null(null, 20); -- 20
   select * from public.float_is_null(null, -20); --  -20
   select * from public.float_is_null(null); -- 0
  */
  IF (sx is NULL) THEN
      IF (sdef is NULL) THEN sdef=0.0; END IF;
      RETURN sdef;
  ELSE
    RETURN sx;
  END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.float_is_null(double precision, double precision) IS 'Контроль значения типа double precision (проверка на NULL и замена NULL на значение по умочанию)';

CREATE OR REPLACE FUNCTION public.bool_is_null(
    sx boolean,
    sdef boolean DEFAULT false)
  RETURNS boolean AS
$BODY$
BEGIN
  -- Контроль значения типа boolean (проверка на NULL и замена NULL на значение по умочанию)
  /*
    select * from bool_is_null(null);
    select * from bool_is_null(null, true);
    select * from bool_is_null(null, null);
  */
  IF (sx is NULL) THEN
      IF (sdef is NULL) THEN sdef=false; END IF;
      RETURN sdef;
  ELSE
    RETURN sx;
  END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.bool_is_null(boolean, boolean) IS 'Контроль значения типа boolean (проверка на NULL и замена NULL на значение по умочанию)';

CREATE OR REPLACE FUNCTION public.its_bigint(
    sx character varying,
    yes_sign boolean DEFAULT true)
  RETURNS boolean AS
$BODY$
DECLARE
  res boolean;
  v float;
  b boolean;
BEGIN
/*
    Проверка, что строка - это целое число (bigint)
    sx - тестируемая строка
    yes_sign - флаг. Если >0, то учитывать знак (+/-)
    Пример вызова:
     select * from its_bigint('-127739.',true); -- false
     select * from its_bigint('-127.739',true); -- false
     select * from its_bigint('-127739',true);  -- true
     select * from its_bigint('-127739',false); -- false
     select * from its_bigint('127739',false);  -- true
  */
  res = false;
  sx = str_is_null(sx);
  IF (sx <> '') THEN
     IF (position('.' in sx)<=0) THEN
        IF (position(',' in sx)<=0) THEN
           b = its_float(sx,'',yes_sign,false,false);
           IF (b=TRUE) THEN
              res = true;
              V = CAST(sx as float);
              IF (v < -9223372036854775808) THEN
                 res = false;
              ELSE
                 IF (v > 9223372036854775807) THEN
                    res = false;
                 END IF;
              END IF;
           END IF;
        END IF;
     END IF;
  END IF;
  RETURN res;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.its_bigint(character varying, boolean) IS 'Проверка, что строка - это целое число (bigint)';

CREATE OR REPLACE FUNCTION public.its_integer(
    sx character varying,
    yes_sign boolean DEFAULT true)
  RETURNS boolean AS
$BODY$
DECLARE
  res boolean;
  v float;
  -- k integer;
  b boolean;
BEGIN
/*
    Проверка, что строка - это целое число (integer)
    sx - тестируемая строка
    yes_sign - флаг. Если >0, то учитывать знак (+/-)
    Пример вызова:
       select * from its_integer('-127739.',true); -- false
       select * from its_integer('-127.739',true); -- false
       select * from its_integer('-127739',true);  -- true
       select * from its_integer('-127739',false); -- false
       select * from its_integer('127739',false);  -- true
  */
  res = false;
  sx = str_is_null(sx);
  IF (sx <> '') THEN
     IF (position('.' in sx)<=0) THEN
        IF (position(',' in sx)<=0) THEN
           b = its_float(sx,'',yes_sign,false,false);
           IF (b=TRUE) THEN
              res = true;
              V = CAST(sx as float);
              IF (v < -2147483648) THEN
                 res = false;
              ELSE
                 IF (v > 2147483647) THEN
                    res = false;
                 END IF;
              END IF;
           END IF;
        END IF;
     END IF;
  END IF;
  RETURN res; 
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.its_integer(character varying, boolean) IS 'Проверка, что строка - это целое число (integer)';

CREATE OR REPLACE FUNCTION public.its_float(
    sx character varying,
    ch_point character varying DEFAULT '.,'::character varying,
    yes_sign boolean DEFAULT true,
    yes_point_start boolean DEFAULT false,
    yes_point_end boolean DEFAULT false)
  RETURNS boolean AS
$BODY$
DECLARE
   i integer;  
   m integer;
   ch varchar(1);
   c integer;
   cp integer;
   ch_ok varchar(20);
   v float;
   res boolean;
BEGIN
  /*
    Проверка, что строка - это число (float)
    sx - тестируемая строка
    ch_point - строка символов-разделителей целой и дробной части числа (точка и запятая)...
    yes_sign - флаг. Если >0, то учитывать знак (+/-)
    yes_point_start - если true, то точка может быть первым символом
    yes_point_end - если true, то точка может быть последним символом
    Пример вызова:
       select * from its_float('-127739.','.',true,false,false);
       select * from its_float('-127739.','.',true,false,true);
       select * from its_float('-1277.39','.',true);  -- Результат:  true
       select * from its_float('-1277.39','.',true);  -- Результат:  true
       select * from its_float('12773,99','.',true,false,true);
       select * from its_float('12773,99',',.',true,false,true); //допустимы два разделителя
       select * from its_float('12773.99','.',true,false,true);
       select * from its_float('0.12773','.',true,false,true);
       select * from its_float('.12773','.',true,false,true);
       select * from its_float('.12773','.',true,true,true);
       select * from its_float('.12773.','.',true,true,true);
  */
  res = false;
  sx = str_is_null(sx); 
  c = CHAR_LENGTH(sx);
  IF (c >0) THEN
     ch_point = str_is_null(ch_point);
     yes_sign = bool_is_null(yes_sign, true);
     yes_point_start = bool_is_null(yes_point_start);
     yes_point_end = bool_is_null(yes_point_end);
     cp = 0;
     ch_ok = '0123456789' || ch_point;
     IF (yes_sign = true) THEN
         ch_ok = ch_ok || '+-';
     END IF;
     res = true;
     i = 0;
     WHILE (i < c) LOOP
       i =  i + 1;
       ch = substring(sx,i,1);
       IF (POSITION(ch in ch_point)>0) THEN
          cp = cp + 1;
          /* ......................................... */
          -- контроль корректности в контексте точки в начале
          IF (yes_point_start = false) THEN
              IF (i = 1) THEN
                 res=false;
                 i = c + 1; /* exit */
              END IF;
          END IF;
          /* ......................................... */
          /* ......................................... */
          -- контроль корректности в контексте точки в конце
          IF (yes_point_end = false) THEN
              IF (i = c) THEN
                 res=false;
                 i = c + 1; /* exit */
              END IF;
          END IF;
          /* ......................................... */
        END IF;
        m = POSITION(ch in ch_ok);
        IF (m <= 0) THEN
           /* встретился запрещенный символ */
           res=false;
           i = c + 1; /* exit */
        ELSE
          /* проверяем знак +/- */                        
          IF (yes_sign = TRUE) THEN
             m = POSITION(ch in '+-');
             IF (m > 0) THEN
                IF (i > 1) THEN
                   /* знак +/- не на своем месте */
                   res=false;
                   i = c + 1; /* exit */
                END IF; 
             END IF; 
          END IF; 
        END IF;         
     END LOOP;
     IF (cp > 1) THEN res = false; END IF;
  END IF; 
  RETURN res;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.its_float(character varying, character varying, boolean, boolean, boolean) IS 'Проверка, что строка - это число (float)';

CREATE OR REPLACE FUNCTION public.its_bool(v character varying)
  RETURNS boolean AS
$BODY$
DECLARE res boolean;
BEGIN
  -- Проверка, что строка - это логическое значение (boolean)
  -- select * from its_bool('9');
  -- select * from its_bool('ФальшЬ');
  -- select * from its_bool('Y');
  -- select * from its_bool('Д');
  -- select * from its_bool('N');
  -- select * from its_bool('Nt');
  -- select * from its_bool('Н');
  -- select * from its_bool('.t.');  
  res = false;
  v = upper(str_is_null(v));
  if (v = '.T.') THEN res = true; END IF;
  if (v = 'T') THEN res = true; END IF;
  if (v = 'TRUE') THEN res = true; END IF;
  if (v = '.F.') THEN res = true; END IF;
  if (v = 'F') THEN res = true; END IF;
  if (v = 'FALSE') THEN res = true; END IF;
  if (v = 'Y') THEN res = true; END IF;
  if (v = 'YES') THEN res = true; END IF;
  if (v = 'YE') THEN res = true; END IF;
  if (v = 'N') THEN res = true; END IF;
  if (v = 'NOT') THEN res = true; END IF;
  if (v = 'NO') THEN res = true; END IF;
  if (v = '1') THEN res = true; END IF;
  if (v = '0') THEN res = true; END IF;
  if (v = '+') THEN res = true; END IF;
  if (v = '-') THEN res = true; END IF;
  if (v = 'OK') THEN res = true; END IF;
  if (v = 'CANCEL') THEN res = true; END IF;
  if (v = 'Д') THEN res = true; END IF;
  if (v = 'ДА') THEN res = true; END IF;
  if (v = 'Н') THEN res = true; END IF;
  if (v = 'НЕ') THEN res = true; END IF;
  if (v = 'НЕТ') THEN res = true; END IF;
  if (v = 'ИСТИНА') THEN res = true; END IF;
  if (v = 'И') THEN res = true; END IF;  
  if (v = 'ФАЛЬШ') THEN res = true; END IF;
  if (v = 'ФАЛЬШЬ') THEN res = true; END IF;
  if (v = 'Ф') THEN res = true; END IF;  
  RETURN res;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.its_bool(character varying) IS 'Проверка, что строка - это логическое значение (boolean)';

CREATE OR REPLACE FUNCTION public.bool_as_str(
    v boolean,
    nr integer DEFAULT 0)
  RETURNS character varying AS
$BODY$
DECLARE res character varying;
BEGIN
  /* 
    Конвертировать логическое значение в строку
    select * from bool_as_str(true);
    select * from bool_as_str(true, 1);
    select * from bool_as_str(true, 2);
    select * from bool_as_str(true, 3);
    select * from bool_as_str(true, 4);
  */
  res = '';
  v = bool_is_null(v);
  nr = int_is_null(nr);
  IF (nr<0) THEN nr=0; END IF;
  IF (nr>4) THEN nr=4; END IF;
  IF (v = true) THEN
     IF (nr=0) THEN res = 'True' ; END IF;
     IF (nr=1) THEN res = 'T' ; END IF;
     IF (nr=2) THEN res = '1' ; END IF;
     IF (nr=3) THEN res = 'Истина' ; END IF;
     IF (nr=4) THEN res = 'Да' ; END IF;
  ELSE
     IF (nr=0) THEN res = 'False' ; END IF;
     IF (nr=1) THEN res = 'F' ; END IF;
     IF (nr=2) THEN res = '0' ; END IF;
     IF (nr=3) THEN res = 'Фальш' ; END IF;
     IF (nr=4) THEN res = 'Нет' ; END IF;
  END IF;
  res = str_is_null(res);  
  RETURN res;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.bool_as_str(boolean, integer) IS 'Конвертировать логическое значение в строку';

CREATE OR REPLACE FUNCTION public.bigint_as_str(v bigint)
  RETURNS character varying AS
$BODY$
DECLARE
  res character varying;
BEGIN
  -- Конвертировать bigint в строку
  -- select * from bigint_as_str(12);
  -- select * from bigint_as_str(-999999999);
  res = '0';
  v = bigint_is_null(v);
  res = cast(v as character varying);
  res = str_is_null(res);
  RETURN res;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.bigint_as_str(bigint) IS 'Конвертировать bigint в строку';

CREATE OR REPLACE FUNCTION public.int_as_str(v integer)
  RETURNS character varying AS
$BODY$
DECLARE
  res character varying;
BEGIN
  -- Конвертировать integer в строку
  -- select * from int_as_str(12);
  -- select * from int_as_str(-22759881);
  res = '0';
  v = int_is_null(v);
  res = cast(v as character varying);
  res = str_is_null(res);
  RETURN res;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.int_as_str(integer) IS 'Конвертировать integer в строку';

CREATE OR REPLACE FUNCTION public.float_as_str(v double precision)
  RETURNS character varying AS
$BODY$
DECLARE
  res character varying;
BEGIN
  -- Конвертировать double precision в строку
  -- select * from float_as_str(12.5);
  -- select * from float_as_str(12);
  -- select * from float_as_str(-99999.99999);
  res = 0;
  v = float_is_null(v);
  res = cast(v as character varying);
  res = str_is_null(res);
  RETURN res;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.float_as_str(double precision) IS 'Конвертировать double precision в строку';

CREATE OR REPLACE FUNCTION public.str_as_bool(v character varying)
  RETURNS boolean AS
$BODY$
DECLARE res boolean;
BEGIN
  -- Конвертировать строку в boolean
  /*
     select * from str_as_bool('да');
     select * from str_as_bool('Д');
     select * from str_as_bool('+');
     select * from str_as_bool('Y');
     select * from str_as_bool(null);
     select * from str_as_bool('Привет');
  */
  res = false;
  v = upper(str_is_null(v));
  if (v = '.T.') THEN res = true; END IF;
  if (v = 'T') THEN res = true; END IF;
  if (v = 'TRUE') THEN res = true; END IF;
  if (v = 'Y') THEN res = true; END IF;
  if (v = 'YES') THEN res = true; END IF;
  if (v = 'YE') THEN res = true; END IF;
  if (v = '1') THEN res = true; END IF;
  if (v = '+') THEN res = true; END IF;
  if (v = 'OK') THEN res = true; END IF;
  if (v = 'Д') THEN res = true; END IF;
  if (v = 'ДА') THEN res = true; END IF;
  if (v = 'ИСТИНА') THEN res = true; END IF;
  if (v = 'И') THEN res = true; END IF;  
  RETURN res;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.str_as_bool(character varying) IS 'Конвертировать строку в boolean';

CREATE OR REPLACE FUNCTION public.str_as_bigint(
    v character varying,
    v_def bigint DEFAULT 0)
  RETURNS bigint AS
$BODY$
DECLARE res bigint;
BEGIN
  -- Конвертировать строку в bigint
  -- select * from public.str_as_bigint('125', -99);
  -- select * from public.str_as_bigint('a125', -99);
  -- select * from public.str_as_bigint('125.', -99);
  -- select * from public.str_as_bigint('.125', -99);
  -- select * from public.str_as_bigint('1.25', -99);
  -- select * from public.str_as_bigint('1,25', -99);
  v_def = bigint_is_null(v_def);
  res = v_def;
  v = str_is_null(v);
  IF (its_bigint(v) = true) THEN 
     res = cast(v as bigint);
  END IF;
  res = bigint_is_null(res);
  RETURN res;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.str_as_bigint(character varying, bigint) IS 'Конвертировать строку в bigint';

CREATE OR REPLACE FUNCTION public.str_as_int(
    v character varying,
    v_def integer DEFAULT 0)
  RETURNS integer AS
$BODY$
DECLARE res integer;
BEGIN
  -- Конвертировать строку в integer
  -- select * from public.str_as_int('125', -99);
  -- select * from public.str_as_int('a125', -99);
  v_def = int_is_null(v_def);
  res = v_def;
  v = str_is_null(v);
  IF (its_integer(v)) THEN 
     res = cast(v as integer);
  END IF;
  res = int_is_null(res);
  RETURN res;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.str_as_int(character varying, integer) IS 'Конвертировать строку в integer';

CREATE OR REPLACE FUNCTION public.str_as_float(
    v character varying,
    v_def double precision DEFAULT 0.0)
  RETURNS double precision AS
$BODY$
DECLARE res double precision;
BEGIN
  -- Конвертировать строку в double precision
  -- select * from public.str_as_float('1.125', -99);
  -- select * from public.str_as_float('a1.125', -99);
  v_def = float_is_null(v_def);
  res = v_def;
  v = str_is_null(v);
  IF (its_float(v)) THEN 
     res = cast(v as double precision);
  END IF;
  res = float_is_null(res);
  RETURN res;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.str_as_float(character varying, double precision) IS 'Конвертировать строку в double precision';

Скачать PG-backup (SQL-текст функций)

Продолжение следует…

На предыдущую страницу…

Дата: 10.12.2021

Программируй и разрабатывай

Программируй и разрабатывай

PostgreSQL. Функции. Контроль и преобразование значений