Использование функций в PostgreSQL как параметризированных представлений
В ежедневной работе часто встает задача ясно и просто ссылаться на большие списки колонок и выражений в выборке, и/или обходиться с громоздкими и неясными условиями в предложении where . Обычно для этих целей используются представления, что вполне удобно и наглядно. Можно сравнить запрос:
который достаточно ясно воспринимается как «берем активных пользователей и получаем по ним детальную информацию» и этот же запрос, но, так сказать, в развернутом виде:
Запросы подобного вида — с большим списком получаемых колонок и выражений на их основе, со сложными условиями и которые в реальной жизни нередко отягчены историческими напластованиями — зачастую совершенно нечитаемы и малопонятны. Наверное, стоит заметить, что само изменение понятия «активный» (например, убрать или добавить удаленных работников или сотрудников в декретном отпуске и т.п.) может стать не то чтобы нетривиальным, но очень утомительным занятием; да и на количестве ошибок оно вряд ли скажется достаточно благоприятно; и изменение списка колонок или просто выражения влечет за собой схожие последствия. Пожалуй, можно сказать, что если для таблиц выражение select * from table строго неприемлемо, то для представлений подобного вида оно, наверное, даже предпочтительно. Ну для некоторых, по крайней мере.
Рассмотрим другую задачу. Пусть у нас есть простая таблица пользователей:
и таблица друзей:
Требуется:
Получить определенного пользователя со списком друзей.
Так как эта операция требуется достаточно часто, создаем для нее представление:
Все хорошо, но появилось новое требование: получить пользователя со списком друзей, которые одновременно являются друзьями другого пользователя (например, просматривающего):
К сожалению, создать представление на основе этого запроса невозможно — передать идентификатор второго пользователя как параметр нельзя; но есть возможность обойти это ограничение с помощью декартова произведения:
Использование получившегося представления совершенно естественно:
Поступает новое требование: требуется получать не просто общих друзей, но общих друзей, зарегистрировавшихся в указанный промежуток времени. Так как создать таблицу со всеми возможными временными промежутками не представляется возможным, то придется создать функцию:
Использование тоже достаточно удобно:
Казалось бы, запрос, использующий эту фунцию, будет работать незамысловато — сначала функция вернет все возможные строки, а потом они будут отфильтрованы по условию. Давайте посмотрим:
Удивительно, но это не так — сервер сумел развернуть функцию непосредственно в тело запроса. Да, Postgresql в ряде случаев умеет внедрять тело функции непосредственно в запрос.
В каких случаях это происходит?
- Функция реализована на SQL ( LANGUAGE SQL ) как простой select , возвращающий скалярный тип
- Функция помечена как immutable или stable
- Функция не содержит подзапросов
- Функция не помечена как security definer
- У функции нет специфических set (н., set enable_seqscan=off и т.п.)
- Функция возвращает только одну колонку
- Возвращаемый тип должен совпадать с типом функции
- И еще ряд ограничений (полный список см. по ссылке ниже)
Это может пригодиться для инкапсуляции несложной, но громоздкой логики, например:
Как видно, никакого вызова функции тут нет — код функции вставился непосредственно в тело запроса. Эту функцию можно рассматривать как своеобразный макрос.
Хотелось бы заодно обратить внимание на компактный синтаксис записи вызова функции — в качестве параметра передается сразу запись, причем принимается не как строго определенный тип ( pg_class в данном случае), а как произвольный тип с колонкой relname .
У табличных функций похожие, но значительно более мягкие ограничения:
- Функция реализована на SQL ( LANGUAGE SQL )
- Функция immutable или stable
- Функция не security definer
- Функция не strict
- Нет специфических set
- Тело функции содержит единственный select (и только select , insert / update / delete не допускаются)
- Типы возвращаемых колонок должны соответствовать типам в объявлении функции
- И еще ряд достаточно специфичных ограничений
Таким образом, реализованное в Postgres встраивание тела функции непосредственно в запрос дает возможность эффективно реализовать отсутствующую в стандарте, но тем не менее востребованную и удобную конструкцию «представление с параметрами».
Интересно, что в DB2 и SQL Server для решения задачи «представление с параметрами» также используются функции, встраиваемые в запрос.
Как вызвать функцию, PostgreSQL
Я пытаюсь использовать функцию с PostgreSQL для сохранения некоторых данных. Вот сценарий создания:
в документации PostreSQL указано, что для вызова функции, которая не возвращает ни одного результирующего набора, достаточно написать только ее имя и свойства. Поэтому я пытаюсь вызвать функцию следующим образом:
но я получаю ошибку ниже:
у меня есть другие функции, которые возвращают resultset. Я использую SELECT * FROM «fnc»(. ) позвонить им, и это работает. Зачем быть Я получаю эту ошибку?
EDIT: я использую pgAdmin III Query tool и пытаюсь выполнить инструкции SQL там.
5 ответов
вызов функции по-прежнему должен быть правильный SQL запрос:
для Postgresql вы можете использовать выполнить. PERFORM действителен только на языке процедур PL/PgSQL.
предложение от команды postgres:
подсказка: если вы хотите отменить результаты выбора, используйте вместо этого PERFORM.
Если ваша функция не хочет ничего возвращать, вы должны объявить ее «return void», а затем вы можете назвать ее так: «perform functionName(parameter. );»
У меня была та же проблема при попытке проверить очень похожую функцию, которая использует инструкцию SELECT, чтобы решить, должна ли быть сделана вставка или обновление. Эта функция была перезаписью хранимой процедуры T-SQL.
Когда я тестировал функцию из окна запроса, я получил ошибку «запрос не имеет назначения для данных результата». Я, наконец, понял, что, поскольку я использовал оператор SELECT внутри функции, я не мог проверить функцию из окна запроса, пока я не назначил результаты выбора локальной переменной с помощью оператора INTO. Это исправило проблему.
если исходная функция в этом потоке была изменена на следующую, она будет работать при вызове из окна запроса,
вы объявляете свою функцию возвращающей boolean, но она никогда ничего не возвращает.
PostgreSQL — Вызов функций
PostgreSQL позволяет вызывать функции с именованными параметрами, используя либо позиционную , либо именованную нотацию. Именованная запись особенно полезна для функций с большим количеством параметров, поскольку она делает связи между параметрами и фактическими аргументами более явными и надежными. В позиционной нотации вызов функции записывается со значениями аргументов в том же порядке, в котором они определены в объявлении функции. В именованной нотации аргументы сопоставляются с параметрами функции по имени и могут быть записаны в любом порядке. Для каждой нотации также учитывайте влияние типов аргументов функций.
В любой нотации параметры со значениями по умолчанию, заданными в объявлении функции, вообще не нужно записывать в вызове. Но это особенно полезно в именованной нотации, поскольку можно опустить любую комбинацию параметров; в то время как в позиционной записи параметры могут быть опущены только справа налево.
PostgreSQL также поддерживает смешанную нотацию, которая сочетает в себе позиционную и именованную нотацию. В этом случае позиционные параметры записываются первыми, а именованные параметры идут после них.
Следующие примеры иллюстрируют использование всех трех обозначений с использованием следующего определения функции:
Функция concat_lower_or_upper имеет два обязательных параметра a и b . Кроме того, есть один необязательный параметр uppercase , который по умолчанию равен false . Входные данные a и b будут объединены и переведены в верхний или нижний регистр в зависимости от uppercase параметра.
Использование позиционной записи
Позиционная нотация — это традиционный механизм передачи аргументов функциям в PostgreSQL . Пример:
Все аргументы указаны по порядку. Результат в верхнем регистре, так uppercase как указывается как true . Другой пример:
Здесь uppercase параметр опущен, поэтому он получает значение по умолчанию false , что приводит к выводу в нижнем регистре. В позиционной нотации аргументы могут быть опущены справа налево, если они имеют значения по умолчанию.
Использование именованной нотации
В именованной нотации имя каждого аргумента указывается с помощью => , чтобы отделить его от выражения аргумента. Например:
Опять же, аргумент uppercase был опущен, поэтому он установлен false неявно. Одним из преимуществ использования именованной нотации является то, что аргументы могут быть указаны в любом порядке, например:
Старый синтаксис, основанный на «: language-plaintext»>SELECT concat_lower_or_upper(a := ‘Hello’, uppercase := true, b := ‘World’); concat_lower_or_upper ———————— HELLO WORLD (1 row)
Использование смешанной записи
Смешанная нотация сочетает в себе позиционную и именованную нотацию. Однако, как уже упоминалось, именованные аргументы не могут предшествовать позиционным аргументам. Например:
В приведенном выше запросе аргументы a и b указываются позиционно, а uppercase указываются по имени. В этом примере это мало что добавляет, кроме документации. Для более сложной функции, имеющей множество параметров со значениями по умолчанию, именованная или смешанная нотация может сократить объем написания и уменьшить вероятность ошибки.