PostgreSQL tips and tricks — различия между версиями

Текущая версия на 04:59, 10 октября 2014

Неточный поиск в ShowCase (БД на postgreSQL)

Иногда бывает нужно найти запись по неточной информации, например, неточно известна фамилия слушателя Факультета Усовершенствования Врачей (ФУВ). Сотрудник деканата ФУВ пытается найти Слушателя: Куманшева Наталья Викторовна, и не находит. На самом деле фамилия слушателя: Кумакшева. Для этого может быть использована функция похожести similarity(text, text) из расширения postgreSQL, которое подключается командой: create extension pg_trgm;

select similarity('Куманшева','Кумакшева') — выдает некий коэффициент, в данном случае: 0,538462. В форме (xForms) делается поле ввода фамилии и кнопка «Найти похожие», по которой вызывается обычный селектор, показывающий записи, удовлетворяющие условию, что этот коэффициент больше определенного порога. Для практических нужд в селекторе достаточно использовать значение 0,3 (если поиск только по фамилии). Будут выданы все ФИО с похожей фамилией (их желательно отсортировать по этому коэффициенту — чем выше коэффициент, тем выше в списке запись).

Запрос SQL 2008, который показывает, какие запросы висят и все блочат

SELECT db.name DBName, tl.request_session_id, wt.blocking_session_id, OBJECT_NAME(p.OBJECT_ID) BlockedObjectName, tl.resource_type, h1.TEXT AS RequestingText, h2.TEXT AS BlockingTest, tl.request_mode FROM sys.dm_tran_locks AS tl INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1 CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

Генерация произвольного количества строк

В postgresql обнаружил прекрасную функцию для генерации любого количества строк, ранее мы создавали дополнительную табличку с чслами для этих целей.

generate_series(start, stop) где start и stop произвольные целые числа, у данной функции существуют ещё две удобные перезагрузки:

   generate_series(start, stop, step) первые два параметра анлогичны, а третий размер шага (в том числе и отрицательный).
   generate_series(start, stop, step) первые два параметра имеют тип timestamp, а третий размер шага (тип interval), например
   generate_series('2008-03-01 00:00'::timestamp,'2008-03-04 12:00', '10 hours').

Длина названий столбцов и таблиц

В postgres есть ограничение на длину имен столбцов и таблиц. Все имена не должны быть больше 63 символов (63 байта данный параметр в принципе можно изменить при сборке postgres)

Более подробно см. ссылку postgresql.ru.net/manual/sql-syntax-lexical.html

Но для кириллических символом данное ограничение более существенно, каждый русский символ эквивалентен 2 обычным. Таким образом невозможно создать таблицу у которой столбец имеет полностью русское имя длиннее 31 символа.

При больших длинах идентификаторов (имена столбцом и таблиц являются идентификаторами) текст будет обрезан.

Данное поведение может наблюдаться в showcase при отображении гридов. Из этой ситуации есть 2 более менее простых выхода

• Уменьшить длину идентификатора (универсальный способ)
• Передавать данные для грида через xml (способ для showcase)

Проверка форматов: число, дата.

Когда возникает необходимость проверки: является ли строка числом или датой, можно воспользоваться следующими функциями:

• isdigit(text)
• isdate(text)

Функции возвращают TRUE или FALSE в зависимости от того, верный ли формат. Вот скрипты для создания этих функций: isdigit :

CREATE OR REPLACE FUNCTION «public».«isdigit»(text)
 RETURNS «pg_catalog».«bool» AS $BODY$
DECLARE
 inputText ALIAS FOR $1;
 tempChar text;
 isNumeric boolean;
BEGIN
 isNumeric = true;
 FOR i IN 1..length(inputText) LOOP
  tempChar := substr(inputText, i, 1);
  IF tempChar ~ '[0-9]' THEN /* do nothing */
  ELSE
   return FALSE;
  END IF;
 END LOOP;
 return isNumeric;
END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE COST 100
;
ALTER FUNCTION «public».«isdigit»(text) OWNER TO «postgres»;
isdate
CREATE OR REPLACE FUNCTION «public».«isdate»(v text)
 RETURNS «pg_catalog».«bool» AS $BODY$
BEGIN
 if v is null then
  return false;
 else
  perform v::date;
  return true;
 end if;
 exception when others then
 return false;
END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE COST 100
;
ALTER FUNCTION «public».«isdate»(v text) OWNER TO «postgres»;

Поиск по тексту хранимых процедур postgresq

Чтобы найти вхождение какой-либо строки в тексте хранимых процедур; например для того, чтобы узнать, откуда происходит вызов процедур, либо убедиться, что на таблицу, которую Вы хотите удалить, ничего не ссылается; можно использовать обращение к таблице pg_catalog.pg_proc, содержащей строковые ресурсы хранимых процедур в колонке prosrc. Найти нужную строчку можно с помощью следующего запроса:

SELECT proname, proargnames, prosrc 
 FROM pg_proc
 WHERE prosrc ILIKE '%search_string%';

где ILIKE — ключевое слово в Postgresql, которое можно использовать вместо LIKE, чтобы находить нечувствительные к регистру вхождения строки.

Также с помощью таблицы pg_catalog.pg_proc можно заменить идентификатор сразу во всех процедурах.

Запоминать открытый пункт навигатора в pgadmin

Для того, чтобы не приходилось каждый раз открывать навигатор, а вместо этого его состояние восстанавливалось из предыдущей сессии нужно: 1.отключится от сервера 2. пойти в properties сервера 3. поставить чекбокс «restore env»

Функция occur. Считает количество вхождений символа в строку.

Сколько раз встречается символ в строке? Функция occur может быть полезна для создания отступа в иерархическом селекторе строка — это заранее сформированный сортировочный код (полезно для больших таблиц типа ОКВЕД — ~ 15000 записей, до 7 уровней) Вызов, например,

select occur('D — DL — 30 — 30.0 — 30.02.000 — 30.02.310 — 30.02.313','-',0)
— — —
DECLARE
        i int4;
BEGIN
i = position(symbol in string);
IF i > 0 THEN
        count = occur(substr(string, i+1), symbol, Coalesce(init_value,0) + 1);
ELSE
        count = Coalesce(init_value,0);
END IF;
END

Parameters: OUT count int4, IN string text, IN symbol varchar, IN init_value int4 Return type: int4

Предотвращение подмены кириллических символов номерами unicode символов при работе с xml в postgresql

SELECT xmlparse(CONTENT $$<element attribute='Ваш русский текст'>value</element>$$)

Решение проблемы с uuid и челестой

1.1) открываем пгадмин, нажимаем sql, копируем туда

 
 CREATE OR REPLACE FUNCTION celesta.uuidequal(a1 uuid, a2 CHARACTER VARYING)
   RETURNS BOOLEAN AS 'select a1::varchar = a2'
   LANGUAGE SQL IMMUTABLE;

 CREATE OPERATOR = (
     LEFTARG = uuid,
     RIGHTARG = CHARACTER VARYING,
     PROCEDURE = celesta.uuidequal
 );

и выполняем. все.

1.2) там же в пгадмине выполняем:

 CREATE CAST (CHARACTER VARYING AS UUID) WITH INOUT AS ASSIGNMENT;