Получаем список операторов PostgreSQL

в 10:30, , рубрики: hints, postgresql, sql, system catalog

Получаем список операторов PostgreSQL - 1

Intro

Эх, давно я не брал в руки шашечек. Но делать нечего, я заперт в чужом городе до вечера и единственным утешением мне служит безалкогольное пиво в местной пиццерии. А потому, чтобы слегка умерщвлить время, напишу статейку о том, как я решал несложную задачу наших переднеконечников (фронтэндеров).

Они в данный момент создают некий интерфейс, в коем, помимо всего прочего, будет редактор фильтров на возвращаемые данные. Потому меня и спросили:

достаточно ли будет в качестве операторов сравнения использовать стандартные "<,>,!=,="? Или есть какие-то операторы, которые стоит добавить?

— Нет, не достаточно, — отвечал я им. — Мало того, что PostgreSQL из коробки поддерживает тьму-тьмущую операторов, так их еще можно определять самому, и вдобавок каждое расширение норовит дополнить систему еще дюжиной.

Мой ответ их озадачил. А когда кто-то озадачен, он с удовольствием озадачит ближнего своего. И меня попросили (как непререкаемого авторитета, само собой) составить список операторов, хотя бы для случая голой и свежей системы.

Но я не первый раз женат, и знаю, что система системе рознь, начиная c различий между версиями ванильного слона, и заканчивая форками, например PostgresPro, CitusDB… тысячи их. Ну, а во-вторых, эти же ребята завтра захотят получать список доступных операторов для отображения, и таки мне все равно придется решать эту задачу. Потому данный текст представляет собой ход моих мыслей.

Постановка задачи

  1. Нам нужны операторы только для фильтра, следовательно результатом применения оператора будет логический тип boolean
  2. Про бинарность или унарность ничего не сказано, значит берем все.

Решение

Моим первым желанием было полезть в мануал и надергать по-быстрому оттуда список основных операторов. Лениво и не спортивно.

Потом посетила мысль прошерстить на этот предмет исходники. Лежит это добро в /src/include/catalog/pg_operator.h. Естественно, мысль тоже пошла лесом. Раз уж мы будем копаться в системных каталогах сервера, то удобнее это делать с помощью SQL.

В пиццерии не оказалось поднятого Postgres сервера, но у меня с собой, как говорится, было. Если же у вас с собой нет, то вы можете провести со мной эти эксперименты на замечательном сервисе SQL Fiddle.

Итак, информация об операторах хранится в системном каталоге pg_operator, потому первой итерацией логично предположить

SELECT * FROM pg_operator;

На моем голом PostgreSQL 9.6 я получил 772 строки, а на том же SQL Fiddle (PostgreSQL 9.3) запрос вернул 823 строки. Такой разброс не должен вас удивлять. Выяснилось, что во втором случае «из коробки» установлено дополнительно 8 расширений:

SELECT extname FROM pg_extension;

|       extname |
|---------------|
|       plpgsql |
| fuzzystrmatch |
|        hstore |
|        intagg |
|      intarray |
|         ltree |
|       pg_trgm |
|     uuid-ossp |
|          xml2 |

Первое приближение

Теперь ограничим выдачу только операторами, которые результатом возвращают boolean.

SELECT * FROM pg_operator WHERE oprresult = 'boolean'::regtype;
---------------
Record Count: 513; (local 9.6)
Record Count: 552; (SQL Fiddle 9.3)

Получаем список операторов PostgreSQL - 2

Разберем эту особую уличную магию. Поле oprresult имеет тип oid, что изнутре представляет собой беззнаковый целочисленный тип в 4-байта. По-хорошему надо было бы и сравнивать с числом, но не все такие ботаны как я, чтобы помнить, что oid типа boolean равен 16. Потому тут мы воспользовались псевдотипом regtype и приведением типов. Очень удобная штучка. Советую взять на вооружение.

Будем резать

Как вы, наверное, заметили один и тот же оператор может использоваться для разных типов аргументов. Нам не нужны дубли по итогу, поэтому попробуем убрать дубли и сделать вывод более осмысленным.

SELECT oprname, 
	count(1), 
	array_agg(DISTINCT oprleft::regtype) AS left_args, 
	array_agg(DISTINCT oprright::regtype) AS right_args, 
	array_agg(DISTINCT obj_description(oid, 'pg_operator')) AS comments
FROM pg_operator 
WHERE oprresult = 'boolean'::regtype 
GROUP BY oprname
ORDER BY count(1) DESC;
---------------
Record Count: 58; (local 9.6)
Record Count: 62; (SQL Fiddle 9.3)

Получаем список операторов PostgreSQL - 3

Автор: Павел Голубь

Источник

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js