Получить имена таблиц с помощью регулярного выражения
Необходимо получить имя таблицы из ввода с помощью запроса sql. Я знаю, что это можно сделать с помощью регулярного выражения, но не уверен, как, следовательно, любая помощь будет оценена по достоинству. Вход:
Выход:
Кстати, postgres 9 больше не поддерживается уже 3 года. Вам следует серьезно подумать об обновлении.
@TimBiegeleisen-Я могу добавить другой столбец, но если вы можете помочь с синтаксическим анализатором/регулярным выражением SQL, это даст мне представление о том, как его написать, поскольку я новичок в postgres.
@Bergi-я использовал его как тег для связи, но мы уже используем обновленную версию. Можете ли вы помочь/дать идею, как написать этот запрос? Спасибо.
@Ram Парсер SQL — это очень большая и сложная программа. Здесь вы можете увидеть, сколько всего нужно сделать PostgreSQL, прежде чем он начнет определять, какую из девяти вещей вы выбираете from. Идентификация ее как цели вставки/обновления/удаления/слияния также требует некоторой работы, и имя таблицы также может быть совершенно допустимым в качестве значения/выражения в списке выбора, условия, аргумента функции и других мест. Будет сложно повторно реализовать все это в регулярном выражении.
@Zegarek-Есть 4 случая вставки, обновления, удаления и выбора. Так что их нужно обработать. Есть ли какой-нибудь образец запроса, с которым вы можете помочь для начала. Я могу его уточнить.
@Zegarek-Я добавил еще несколько комментариев, чтобы узнать, можно ли заменить одинарные и двойные кавычки в строке при сопоставлении с регулярным выражением и просто оставить их в начале и конце, чтобы уменьшить количество ложных срабатываний. Я пробовал это но это не работает. Я дал код в комментарии, если вы можете, пожалуйста, сообщите, нужно ли что-то изменить.
Ответы 1
Этого будет чрезвычайно сложно добиться, используя только регулярное выражение. Если вам действительно нужно сделать это надежно, вы можете посмотреть, как pgpool-II повторно использовал собственный парсер PostgreSQL для своих нужд — это скорее задача, требующая интенсивного использования C++, а не SQL.
Вот еще несколько примеров на вики , некоторые из которых вы можете использовать напрямую. Вы даже можете заставить Python sqlparse обработать текст запроса, а затем .get_identifiers() , и все это внутри базы данных, в функции PL/Python.
Если вы хотите охватить только некоторые очень простые случаи, вы можете попытаться настроить таргетинг токенов по определенным ключевым словам:
select field1
,unnest(
regexp_matches(
field2
,'(?:UPDATE|INTO|FROM|JOIN|USING|TABLE)(?:\s+ONLY)*\s+([[:alpha:]_]+[[:word:]]*|"[^"]+")'
,'gi'
)
) AS spotted_table
from table1;
- ?: in (?:что-то) делает скобки нефиксируемыми, поэтому сообщается только о совпадениях из последней группы, в которой нет
?: gв'gi'делает все совпадения в этом отчете, аiотключает чувствительность к регистру- синтаксис идентификатора взят из документа, за исключением странности
$:Идентификаторы SQL и ключевые слова должны начинаться с буквы (
a-z, а также букв с диакритическими знаками и нелатинских букв) или подчеркивания (_). Последующие символы в идентификаторе или ключевом слове могут быть буквами, символами подчеркивания, цифрами (0-9) или знаками доллара ($).
Этот список ключевых слов ни в коем случае не является исчерпывающим, и, хотя он охватывает ваши примеры, это регулярное выражение чрезвычайно хрупко. Посмотрите некоторые очевидные «слепые пятна» в этой демонстрации.
Это становится все более и более сложным, если вы примете во внимание
- идентификаторы могут быть заключены в двойные кавычки или не заключены в кавычки, что приводит к тому, что они сводятся к нижнему регистру
- строковые константы в одинарных или долларовых кавычках, которые содержат текст, похожий на SQL — полезно, если вы также рассматриваете возможность динамического SQL , в противном случае это приведет к ложным срабатываниям.
- технически, вы можете назвать таблицу
"select from table7 join TABLE8 on waitaminute;"(это все имя таблицы, а не только частьtable7): демо - длинные, разделенные запятыми списки отношений, чередующиеся с источниками, которые не являются отношениями, особенно с подзапросами
- конец строки
//и строчные/многострочные/*...*/комментарии
Если запросы, которые вы анализируете, работают с той же базой данных, в которой вы их храните, вы можете сопоставить это с information_schema.tables . Обратите внимание, что существует 9 разных вещей , которые могут находиться в одном и том же месте тела запроса, и это не считая того факта, что таблица — это всего лишь одна из форм одного из них, отношения . Вы также можете select из view, materialized view, foreign table, partitioned table, все из которых действуют как table только в некоторой степени. Некоторые представления являются обновляемыми, что означает, что вы также можете insert/merge/update/delete из них, как если бы вы напрямую взаимодействовали с их базовой таблицей.
Если вы пытаетесь отслеживать эффективные взаимодействия, вам также придется отслеживать правило и триггер системы, определения представлений и matview, а также routine тела и зависимости.
@zegarek-Спасибо за информацию и за отправную точку... Да, мне нужен только базовый вариант... Я использовал ваш sql, и он дал мне хорошие результаты, однако есть несколько моментов, где он работает неправильно... Например, если у нас есть несколько таблиц, тогда должна быть одна строка с несколькими таблицами, разделенными запятыми. Другой вариант: мне нужно получить целевую таблицу в большинстве случаев, а не исходную.... случаи, когда мы выбрали *from tablename, только в этих случаях мне нужно получить имя таблицы, если ее вставка, обновление, удаление в этом случае фокус только для получения имени целевой таблицы.
@Ram То, что вы перечисляете, я уже продемонстрировал в «очевидных слепых пятнах в этой демонстрации» в ответе выше. Я не предлагаю это как запрошенный синтаксический анализатор на основе регулярных выражений, я хочу сказать, что, хотя кажется, что регулярные выражения вполне способны на все это, на самом деле это становится очень запутанным, очень быстро, до такой степени, что вы действительно лучше потратить время на то, чтобы интегрировать настоящий, правильный парсер, вместо того, чтобы пытаться реализовать его с нуля с помощью неподходящего инструмента. :)
При этом я посмотрю, можно ли добавить списки и насколько уродливым это будет. Обновлено: перечитав ваш комментарий, я понял, что вы просто хотите агрегировать результаты и хранить цели отдельно от источников всякий раз, когда происходит вставка/обновление/удаление. Это довольно просто: демо. Более серьезная проблема заключается в том, что регулярное выражение в настоящее время не поддерживает списки отношений, разделенные запятыми (синтаксис неявного соединения старого стиля), цитирование, псевдонимы, подзапросы и т. д.
Спасибо @Zegarek - я добавил еще один случай в скрипку, но похоже, что он не работает..dbfiddle.uk/n2F-PmKD.I запустил его в базе данных также с кодом, но выдал ОШИБКУ: неверное регулярное выражение: неверно класс персонажа
sql : SELECT field1, unnest(regexp_matches(field1, '(?:FROM|JOIN|USING|TABLE)(?:\s+ONLY)*\s+([[:alpha:]_]+.[[:w ord:]]*|"[^"]+")','gi')) из test.table1, но получаю ошибку: недопустимое регулярное выражение: недопустимый класс символов. У меня есть поиск в Google, но я хотел проверить, так ли это требуется удаление escape-символов?
Возможно ли, что где бы мы ни находились, в демо-версии будет ложное срабатывание, если мы сначала удалим кавычки и оставим их только в начале и в конце, чтобы получить правильный результат. Я пытаюсь реализовать это, но это не работает должным образом, потому что это тоже выглядит как строка содержит выберите, вот почему. select field1 ,regexp_matches(concat(''''||replace (field1,'''','')||'''') ,'(?:UPDATE|INTO)(?:\s+ONLY)* \s+([[:alpha:]_]+[[:word:]]*|"[^"]+")' ,'gi' )из имени таблицы
@Ram Вот еще несколько примеров того, как это делается. Опять же: этот ответ должен был служить аргументом против того, что вы сейчас пытаетесь сделать, демонстрируя, почему не стоит пробовать это с помощью простого регулярного выражения. Вы можете использовать Python sqlparse для обработки текста запроса, а затем .get_identifiers() в функции PL/Python.
Итог: это сложно, еще сложнее с регулярным выражением, но самое главное, это уже делалось несколько раз. Вам действительно лучше использовать одно из легкодоступных, хорошо проверенных и документированных решений.
Для общего решения вам понадобится синтаксический анализатор SQL, а не только регулярное выражение. Я предлагаю вам вставить имя таблицы в новый столбец одновременно с вставкой необработанного SQL.