PostgreSQL. Функции. Временные таблицы | Программируй и разрабатывай

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

Временные таблицы — это существенно мощный инструмент для разработчика.
Особенно в том случае, если формируется адаптивная (максимально гибкая) информационная система.

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

table_tmp_tn_is_ok — проверить, что заданное имя таблицы БД может соответствовать имени ВРЕМЕННОЙ таблицы;
table_tmp_drop — удалить таблицу tn (если она временная);
table_tmp_clear — очистить (удалить строки) таблицу tn (если она временная);
table_tmp_delete — удалить строки из заданной временной таблицы, согласно заданного фильтра;
table_tmp_create — создать временную таблицу (предварительно — предыдущая, с таким же именем, автоматически удаляется);
table_tmp_create_as — создать временную таблицу БД на основе существующей таблицы БД (предварительно — предыдущая, с таким же именем, автоматически удаляется);

См., также, в HTML-формате

CREATE OR REPLACE FUNCTION public.table_tmp_tn_is_ok(tn character varying)
  RETURNS boolean AS
$BODY$
DECLARE
  res boolean;
  k integer;
BEGIN
  -- Проверить, что tn может соответствовать имени ВРЕМЕННОЙ таблицы
  -- select * from table_tmp_tn_is_ok('a333');
  -- select * from table_tmp_tn_is_ok('tmp999.fff_pars');
  -- select * from table_tmp_tn_is_ok('dir.variants_list');
  res = false;
  tn = str_is_null(tn);
  IF (tn <> '') THEN
     tn = lower(tn);
     k = position('.' in tn);
     IF (k<=0) THEN
        k = 0;
        /*
        -- Здесь исключения (т.е., если в схеме public есть таблицы Б.Д., то их нужно указать здесь)
        -- IF (tn = 'props_types') THEN k = 1; END IF;
        */
        IF (k<=0) THEN
            res = true;
        END IF;
     END IF;
  END IF;
  RETURN res;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.table_tmp_tn_is_ok(character varying) IS 'Проверить, что tn может соответствовать имени ВРЕМЕННОЙ таблицы';

CREATE OR REPLACE FUNCTION public.table_tmp_drop(tn character varying)
  RETURNS boolean AS
$BODY$
DECLARE
  res boolean;
BEGIN
  -- Удалить таблицу tn (если она временная)
  -- select * from table_tmp_drop('a333');
  res = false;
  tn = str_is_null(tn);
  IF (table_tmp_tn_is_ok(tn) = true) THEN
     tn = lower(tn);
     res = public.exec_sql_text('drop table if exists ' || tn || ';');
     res = bool_is_null(res);
  END IF;
  RETURN res;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.table_tmp_drop(character varying) IS 'Удалить таблицу tn (если она временная)';

CREATE OR REPLACE FUNCTION public.table_tmp_create(
    tn character varying,
    sqltext text)
  RETURNS boolean AS
$BODY$
DECLARE
  res boolean;
  yesnext boolean;
  k integer;
BEGIN
  -- Создать временную таблицу (предварительно - предыдущая, с таким же именем, автоматически удаляется)
  -- select * from table_tmp_create('a333', 'CREATE temporary TABLE IF NOT EXISTS a333 (id serial, nav_namemax character varying(100)); CREATE INDEX a333_id ON a333(id);');
  -- select * from table_tmp_create('a333', 'CREATE temporary TABLE a333 (id serial, nav_namemax character varying(100)); CREATE INDEX a333_id ON a333(id);');
  -- select * from a333;
  -- select * from public.table_tmp_drop('a333');
  res = false;
  tn = str_is_null(tn);
  IF (table_tmp_tn_is_ok(tn) = true) THEN -- контроль имени таблицы (что это временная)
     tn = lower(tn);
     sqltext = str_is_null(sqltext);
     IF (sqltext <> '') THEN
        yesnext = true;
        -- -------------------------------------------
        -- простейший контроль имени таблицы и намерений программиста в sql-запросе
        k = position('CREATE' in upper(sqltext));
        IF (k<=0) THEN yesnext = false; END IF;
        k = position('TEMPORARY' in upper(sqltext));
        IF (k<=0) THEN yesnext = false; END IF;
        k = position('TABLE' in upper(sqltext));
        IF (k<=0) THEN yesnext = false; END IF;
        k = position(upper(tn) in upper(sqltext));
        IF (k<=0) THEN yesnext = false; END IF;
        -- -------------------------------------------
        -- -------------------------------------------
        IF (yesnext = true) THEN
           yesnext = public.table_tmp_drop(tn); -- удаляем предыдущую, если существует
           res = public.exec_sql_text(sqltext);
           res = bool_is_null(res);
        END IF;
        -- -------------------------------------------
     END IF;
  END IF;
  RETURN res;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.table_tmp_create(character varying, text) IS 'Создать временную таблицу (предварительно - предыдущая, с таким же именем, автоматически удаляется)';

CREATE OR REPLACE FUNCTION public.table_tmp_create_as(
    tn_tmp character varying,
    tn_from character varying,
    sfilter character varying DEFAULT ''::character varying)
  RETURNS boolean AS
$BODY$
DECLARE
  res boolean;
  sqltext text;
BEGIN
  /*
     Создать временную таблицу БД на основе существующей таблицы БД (предварительно - предыдущая, с таким же именем, автоматически удаляется)
     Вх. параметры:
       tn_tmp - имя создаваемой (временной) таблицы БД;
       tn_from - имя таблицы БД, на основе которой создается tn_tmp;
       sfilter - фильтр, для копирования строк из таблицы tn_from в таблицу tn_tmp (необязательный параметр);
     Вых. параметры:
       =TRUE - успешно; =FALSE - ошибка
     Пример-1:
      select * from table_tmp_create_as('a333', 'dir.ot_list');
      select * from a333;
      select * from public.table_tmp_drop('a333');
     Пример-2:
      select * from table_tmp_create('a333', 'CREATE temporary TABLE a333 (id integer, namemax character varying(100));');
      --select * from a333;
      select * from exec_select('id, namemax from a333') as ds(id integer, namemax character varying);
      select exec_sql_text('insert into a333 (id, namemax) values (1,' || q39_lr('Object-1') || ')');
      select exec_sql_text('insert into a333 (id, namemax) values (2,' || q39_lr('Объект-2') || ')');
      select exec_sql_text('insert into a333 (id, namemax) values (3,' || q39_lr('Объект-3') || ')');
      --select * from a333;
      select * from exec_select('id, namemax from a333') as ds(id integer, namemax character varying);
      select * from table_tmp_create_as('a555', 'a333', 'id>1');
      --select * from a555;
      select * from exec_select('id, namemax from a555') as ds(id integer, namemax character varying);
      select * from table_tmp_drop('a333');
  */
  res = false;
  tn_tmp = lower(str_is_null(tn_tmp));
  if (table_tmp_tn_is_ok(tn_tmp) = true) THEN -- контроль имени таблицы (что это временная)
     tn_from = str_is_null(tn_from);
     IF (table_exist( -1, tn_from)= true) THEN
        sqltext = 'create temporary table ' || tn_tmp || ' as select * from ' || tn_from;
        sfilter = str_is_null(sfilter);
        IF (sfilter <> '') THEN
           sqltext = sqltext || ' where ' || sfilter;
        END IF;
        PERFORM public.table_tmp_drop(tn_tmp); -- удаляем предыдущую, если существует
        res = public.exec_sql_text(sqltext);
        res = bool_is_null(res);
     END IF;  END IF;
  RETURN res;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.table_tmp_create_as(character varying, character varying, character varying) IS 'Создать временную таблицу БД на основе существующей таблицы БД (предварительно - предыдущая, с таким же именем, автоматически удаляется)';

CREATE OR REPLACE FUNCTION public.table_tmp_clear(tn character varying)
  RETURNS boolean AS
$BODY$
DECLARE
  res boolean;
BEGIN
  -- Очистить (удалить строки) таблицу tn (если она временная)
  -- select * from table_tmp_clear('a333');
  res = false;
  tn = str_is_null(tn);
  if (table_tmp_tn_is_ok(tn) = true) THEN
     tn = lower(tn);
     res = public.exec_sql_text('delete from ' || tn || ';');
     res = bool_is_null(res);
  END IF;
  RETURN res;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.table_tmp_clear(character varying) IS 'Очистить таблицу tn (если она временная)';

CREATE OR REPLACE FUNCTION public.table_tmp_delete(
    tn character varying,
    sfilter character varying)
  RETURNS boolean AS
$BODY$
DECLARE
  res boolean;
BEGIN
  -- Удалить из таблицы tn (если она временная) строки, согласно фильтра sfilter
  /*
  Пример.
    select * from table_tmp_create('a333', 'CREATE temporary TABLE a333 (id integer, namemax character varying(100));');
    --select * from a333;
    select * from exec_select('id, namemax from a333') as ds(id integer, namemax character varying);
    select exec_sql_text('insert into a333 (id, namemax) values (1,' || q39_lr('Object-1') || ')');
    select exec_sql_text('insert into a333 (id, namemax) values (2,' || q39_lr('Объект-2') || ')');
    select exec_sql_text('insert into a333 (id, namemax) values (3,' || q39_lr('Объект-3') || ')');
    --select * from a333;
    select * from exec_select('id, namemax from a333') as ds(id integer, namemax character varying);    
    select * from table_tmp_delete('a333', 'id=2');
    --select * from a333;
    select * from exec_select('id, namemax from a333') as ds(id integer, namemax character varying);    
    select * from table_tmp_drop('a333');
  */
  res = false;
  sfilter = str_is_null(sfilter);
  IF (sfilter <> '') THEN
     tn = str_is_null(tn);
     IF (table_tmp_tn_is_ok(tn) = true) THEN
        tn = lower(tn);
        res = public.exec_sql_text('delete from ' || tn || ' where ' || sfilter || ';');
        res = bool_is_null(res);
     END IF;
  END IF;
  RETURN res;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.table_tmp_delete(character varying, character varying) IS 'Удалить из таблицы tn (если она временная) строки, согласно фильтра sfilter';

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

См., также, в HTML-формате

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

Дата: 26.01.2022