Postgresql вызов функции: PostgreSQL : Документация: 9.6: 4.3. Вызов функций : Компания Postgres Professional

Содержание

основы SQL/PLpgSQL. Базовый курс в Екатеринбурге

Курс «СУБД PostgreSQL: основы SQL/PLpgSQL. Базовый курс» готовит разработчиков баз данных к управлению данными в СУБД PostgreSQL на основе SQL запросов, а также к разработке приложений под PostgreSQL на основе использования языков SQL и PLpgSQL. В рамках курса рассматривается соответствующее программное обеспечение PostgreSQL. Для закрепления материала в курсе проводятся лабораторные занятия.

Стоимость курса

33 898 руб

Заказать

Вы научитесь:

проектировать и создавать структуру базы данных;

использовать инструкции SQL для выборки, изменения и удаления данных;

использовать различные типы данных и функции в запросах SQL и PL/pgSQL;

разрабатывать и отлаживать блоки кода на языке PL/pgSQL;

конструировать код запросов динамически из кода приложения;

настраивать триггеры, проводить перехват ошибок в исполняемом коде.

Аудитория:

Разработчики баз данных с минимальным опытом или без опыта работы с PostgreSQL

Требования к предварительной подготовке слушателя:

Модуль 1. Введение в PostgreSQL

Установка и управление сервером

Настройка параметров сервера

Подключение с помощью psql

Лабораторная работа 1. Подключение к СУБД с помощью клиента psql

Модуль 2. Структура PostgreSQL

Архитектура PostgreSQL

Многоверсионность, блокировки и транзакции

Структура буферного кэша

Журналирование

Лабораторная работа 2.1. Многоверсионность и блокировки

Лабораторная работа 2.2. Работа с кэшем и журналами

Модуль 3. Физическая структура данных

Файлы данных и табличные пространства

Слои

Технология TOAST

Лабораторная работа 3. Работа с табличными пространствами

Модуль 4. Логическая структура данных

База данных

Схема данных

Системный каталог

Лабораторная работа 4. Работа со схемой данных

Модуль 5. Проектирование приложения

Принципы проектирования структуры базы данных

Требования форм нормализации данных

Разработка схемы данных приложения

Лабораторная работа 5. Разработка схемы данных приложения

Модуль 6. Введение в язык SQL

Основные понятия. Общий синтаксис команд

Типы данных

Работа с таблицами

Описание типов данных, ограничения.

Связи между таблицами

Представления данных

Лабораторная работа 6. Работа с таблицами базы данных

Модуль 7. Формирование запросов SQL к базе данных

Лексическая структура, выражения

Запросы на выборку данных

Использование связей между таблицами в запросах

Модифицирующие запросы

Запросы на удаление данных

Лабораторная работа 7. Работа с запросами

Модуль 8. Обращение к данным из клиентских приложений

Протокол для обращения к БД

Работа с транзакциями

Интерфейс пользователя

Лабораторная работа 8. Работа с представлениями

Модуль 9. Использование функций в SQL

Функции: параметры и возвращаемые значения

Вызов функций в запросах

Подстановки в запросах

Ограничения

Лабораторная работа 9. Использование функций в запросах

Модуль 10. Работа с составными типами данных

Составные типы и их параметры

Функции, возвращающие составной тип

Варианты вызова функций

Лабораторная работа 10. Использование составных типов в функциях запросов

Модуль 11. Основы языка PL/pgSQL

Общие сведения о процедурных языках

Блоки и их структура

Работа с переменными и выражениями

Использование функций в PL/pgSQL

Ветвления и циклы

Лабораторная работа 11. Написание функций в PL/pgSQL

Модуль 12. Работа с запросами SQL в PL/pgSQL

Выполнение инструкций SQL

Подстановка переменных

Использование табличных функций

Лабораторная работа 12. Использование запросов в блоках PL/pgSQL

Модуль 13. Использование курсоров в PL/pgSQL

Общие сведения о курсорах

Описание и открытие курсора

Использование курсоров для обработки данных

Операции с курсорами

Лабораторная работа 13. Работа с курсорами

Модуль 14. Динамическое построение запросов

Назначение и возможности динамических запросов

Выполнение динамического запроса

Конструкции динамических запросов

Лабораторная работа 14. Динамические запросы

Модуль 15. Работа с массивами

Общие сведения о массивах

Использование функций для обработки массивов

Работа с массивами средствами PL/pgSQL

Лабораторная работа 15. Работа с массивами

Модуль 16. Исключения: перехват ошибок

Необходимость работы с исключениями

Перехват ошибок

Журналирование ошибок

Уровни обработки ошибок

Лабораторная работа 16. Работа с исключениями

Модуль 17. Использование триггеров

Общие сведения о триггерах

Функции-обработчики триггеров

Варианты использования триггеров

Триггеры, срабатывающие по событиям

Лабораторная работа 17. Настройка триггеров

Модуль 18. Отладка программного кода

Способы отладки

Использование служебных сообщений для отладки

Трассировка

Лабораторная работа 18. Отладка кода приложения

Модуль 19. Разграничение доступа

Пользователи и роли

Привилегии

Политики защиты записей

Разграничение подключений к серверу

Лабораторная работа 19. Управление ролями и привилегиями

Работа с PostgreSQL в Python

17 Ноя. 2018, Python, 249337 просмотров, How to Work with PostgreSQL in Python

PostgreSQL, пожалуй, это самая продвинутая реляционная база данных в мире Open Source Software. По своим функциональным возможностям она не уступает коммерческой БД Oracle и на голову выше собрата MySQL.

Если вы создаёте на Python веб-приложения, то вам не избежать работы с БД. В Python самой популярной библиотекой для работы с PostgreSQL является psycopg2. Эта библиотека написана на Си на основе libpq.

Установка

Тут всё просто, выполняем команду:

pip install psycopg2

Для тех, кто не хочет ставить пакет прямо в системный python, советую использовать pyenv для отдельного окружения. В Unix системах установка psycopg2 потребует наличия вспомогательных библиотек (libpq, libssl) и компилятора. Чтобы избежать сборки, используйте готовый билд:

pip install psycopg2-binary

Но для production среды разработчики библиотеки рекомендуют собирать библиотеку из исходников.

Начало работы

Для выполнения запроса к базе, необходимо с ней соединиться и получить курсор:

import psycopg2
conn = psycopg2.connect(dbname='database', user='db_user', 
                        password='mypassword', host='localhost')
cursor = conn.cursor()

Через курсор происходит дальнейшее общение в базой.

cursor.execute('SELECT * FROM airport LIMIT 10')
records = cursor. fetchall()
...
cursor.close()
conn.close()

После выполнения запроса, получить результат можно несколькими способами:

cursor.fetchone() — возвращает 1 строку
cursor.fetchall() — возвращает список всех строк
cursor.fetchmany(size=5) — возвращает заданное количество строк

Также курсор является итерируемым объектом, поэтому можно так:

for row in cursor:
    print(row)

Хорошей практикой при работе с БД является закрытие курсора и соединения. Чтобы не делать это самому, можно воспользоваться контекстным менеджером:

from contextlib import closing
with closing(psycopg2.connect(...)) as conn:
    with conn.cursor() as cursor:
        cursor.execute('SELECT * FROM airport LIMIT 5')
        for row in cursor:
            print(row)

По умолчанию результат приходит в виде кортежа. Кортеж неудобен тем, что доступ происходит по индексу (изменить это можно, если использовать NamedTupleCursor). Если хотите работать со словарём, то при вызове .cursor передайте аргумент cursor_factory:

from psycopg2.extras import DictCursor
with psycopg2.connect(...) as conn:
    with conn.cursor(cursor_factory=DictCursor) as cursor:
        ...

Формирование запросов

Зачастую в БД выполняются запросы, сформированные динамически. Psycopg2 прекрасно справляется с этой работой, а также берёт на себя ответственность за безопасную обработку строк во избежание атак типа SQL Injection:

cursor.execute('SELECT * FROM airport WHERE city_code = %s', ('ALA', ))
for row in cursor:
    print(row)

Метод execute вторым аргументом принимает коллекцию (кортеж, список и т.д.) или словарь. При формировании запроса необходимо помнить, что:

Плейсхолдеры в строке запроса должны быть %s~~, даже если тип передаваемого значения отличается от строки, всю работу берёт на себя psycopg2.~~
Не нужно обрамлять строки в одинарные кавычки.
Если в запросе присутствует знак %, то его необходимо писать как %%.

Именованные аргументы можно писать так:

>>> cursor.execute('SELECT * FROM engine_airport WHERE city_code = %(city_code)s',
                   {'city_code': 'ALA'})
...

Модуль psycopg2.sql

Начиная с версии 2.7, в psycopg2 появился модуль sql. Его цель — упростить и обезопасить работу при формировании динамических запросов. Например, метод execute курсора не позволяет динамически подставить название таблицы.

>>> cursor.execute('SELECT * FROM %s WHERE city_code = %s', ('airport', 'ALA'))
psycopg2.ProgrammingError: ОШИБКА:  ошибка синтаксиса (примерное положение: "'airport'")
LINE 1: SELECT * FROM 'airport' WHERE city_code = 'ALA'

Это можно обойти, если сформировать запрос без участия psycopg2, но есть высокая вероятность оставить брешь (привет, SQL Injection!). Чтобы обезопасить строку, воспользуйтесь функцией psycopg2. extensions.quote_ident, но и про неё легко забыть.

from psycopg2 import sql
...
>>> with conn.cursor() as cursor:
        columns = ('country_name_ru', 'airport_name_ru', 'city_code')
        stmt = sql.SQL('SELECT {} FROM {} LIMIT 5').format(
            sql.SQL(',').join(map(sql.Identifier, columns)),
            sql.Identifier('airport')
        )
        cursor.execute(stmt)
        for row in cursor:
            print(row)
('Французская Полинезия', 'Матайва', 'MVT')
('Индонезия', 'Матак', 'MWK')
('Сенегал', 'Матам', 'MAX')
('Новая Зеландия', 'Матамата', 'MTA')
('Мексика', 'Матаморос', 'MAM')

Транзакции

По умолчанию транзакция создаётся до выполнения первого запроса к БД, и все последующие запросы выполняются в контексте этой транзакции. Завершить транзакцию можно несколькими способами:

закрыв соединение conn.close()
удалив соединение del conn
вызвав conn.commit() или conn. rollback()

Старайтесь избегать длительных транзакций, ни к чему хорошему они не приводят. Для ситуаций, когда атомарные операции не нужны, существует свойство autocommit для connection класса. Когда значение равно True, каждый вызов execute будет моментально отражен на стороне БД (например, запись через INSERT).

with conn.cursor() as cursor:
    conn.autocommit = True
    values = [
        ('ALA', 'Almaty', 'Kazakhstan'),
        ('TSE', 'Astana', 'Kazakhstan'),
        ('PDX', 'Portland', 'USA'),
    ]
    insert = sql.SQL('INSERT INTO city (code, name, country_name) VALUES {}').format(
        sql.SQL(',').join(map(sql.Literal, values))
    )
    cursor.execute(insert)

💌 Присоединяйтесь к рассылке

Понравился контент? Пожалуйста, подпишись на рассылку.

Please enable JavaScript to view the comments powered by Disqus.

Список определяемых пользователем функций (UDF) в базе данных PostgreSQL

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

Примечание

В PostgreSQL 11 введены процедуры, поэтому нам нужно различать процедуры и функции. В этом случае используйте: PostgreSQL 11 Query

Универсальный

Запрос

 выберите n.nspname как function_schema,
       p.proname как имя_функции,
       l.lanname как function_language,
       случай, когда l.lanname = 'internal', тогда p.prosrc
            иначе pg_get_functiondef(p.oid)
            конец как определение,
       pg_get_function_arguments(p.oid) как function_arguments,
       t.typname как return_type
из pg_proc p
левое соединение pg_namespace n на p.pronamespace = n.oid
левое соединение pg_language l на p.prolang = l.oid
левое соединение pg_type t на t.oid = p.prorettype
где n.nspname не в ('pg_catalog', 'information_schema')
порядок по function_schema,
         имя_функции;

Столбцы

function_schema — имя схемы, содержащей функцию
имя_функции — имя функции
function_language — язык, на котором написана функция
определение — фактический исходный код функции для интерпретируемых языков, символ ссылки или имя файла (в зависимости от языка)
function_arguments — тип аргументов функции
return_type — тип возврата функции

Ряды

Одна строка — представляет одну функцию
Объем строк: — все функции в базе данных
Заказано по — схема функции, имя функции

Результаты проб

PostgreSQL 11+

Запрос

 выберите n. nspname как имя_схемы,
       p.proname как определенное_имя,
       чехол п.прокинд
            когда 'f', то 'ФУНКЦИЯ'
            когда "p", то "ПРОЦЕДУРА"
            когда «а», то «ОБЪЕДИНИТЬ»
            когда 'w', то 'WINDOW'
            конец как добрый,
       l.lanname как язык,
       случай, когда l.lanname = 'internal', тогда p.prosrc
            иначе pg_get_functiondef(p.oid)
            конец как определение,
       pg_get_function_arguments(p.oid) в качестве аргументов,
       t.typname как return_type
из pg_proc p
левое соединение pg_namespace n на p.pronamespace = n.oid
левое соединение pg_language l на p.prolang = l.oid
левое соединение pg_type t на t.oid = p.prorettype
где n.nspname не в ('pg_catalog', 'information_schema')
порядок по имени_схемы,
         конкретное_имя;

Столбцы

имя_схемы — имя схемы, содержащей функцию/процедуру
Specific_name — имя функции/процедуры
вид :
- ФУНКЦИЯ — нормальная функция
- ПРОЦЕДУРА — процедура
- АГРЕГАТ — агрегатная функция
- ОКНО — оконная функция
язык — язык, на котором написана функция/процедура
определение — фактический исходный код функции/процедуры для интерпретируемых языков, символ ссылки или имя файла (в зависимости от языка)
аргументы — аргументы функции/процедуры
return_type — тип возврата

Ряды

Одна строка — представляет одну функцию или процедуру
Область действия строк: — все функции и процедуры в базе данных
Заказ — имя схемы, конкретное имя

Результаты проб

Комментарии видны только в том случае, если посетитель дал согласие на использование статистических файлов cookie.
Чтобы увидеть и добавить комментарии, пожалуйста, примите файлы cookie статистики.

Набор функций возврата и PostgreSQL 10

PostgreSQL 10 теперь доступен для всех, на что намекает Дэвид
Fetter Мне пришлось пересмотреть мою предыдущую статью о Json и
SQL для адаптации к Набор функций возврата
изменения.

Функция возврата набора — это хранимая процедура PostgreSQL , которую можно
используется как отношение: из одного вызова он возвращает весь набор результатов, намного
как подзапрос или таблица.

Раньше можно было использовать SRF в предложении SELECT с сомнительными
(но иногда полезная) семантика, а также в скалярных контекстах.
семантика была исправлена и теперь стала намного понятнее, а использование в скалярных
контексты запрещены — они были хаком и в любом случае никогда не имели смысла.

В частности, следующее изменение в PostgreSQL 10 влияет на запрос, написанный
в предыдущем сообщении в блоге. Это изменение является частью выпуска PostgreSQL 10.
Примечания и
читает: Изменить реализацию функций, возвращающих набор, появляющихся в
список SELECT запроса (Андрес Фройнд) .

Примечания к выпуску расширяются до:

Функции, возвращающие множество, теперь оцениваются перед вычислением скаляра.
выражения в списке SELECT, как если бы они были помещены в
БОКОВОЙ ОТ-статья пункта. Это обеспечивает более разумную семантику для случаев, когда
присутствует несколько функций, возвращающих набор. Если они возвращаются разными
количество строк, более короткие результаты расширяются, чтобы соответствовать самым длинным
результат путем добавления нулей. Раньше результаты циклировались до тех пор, пока не все
завершается одновременно, производя количество строк, равное наименьшему
общее кратное периодов функций. Кроме того, набор-возврат
функции теперь запрещены в конструкциях CASE и COALESCE. Для большего
информацию см. в разделе 37.4.8.

Документация Раздел 37. 4.8 — Возврат функций SQL
Наборы
и дает все необходимые детали, чтобы понять особенность и выгоду
из него в код вашего приложения, как обычно в PostgreSQL Fine Manual.

Вот ранее написанный запрос:

 select case jsonb_typeof(booster)
              когда "массив"
              затем initcap (jsonb_array_elements_text (бустер))
              иначе initcap(бустер #>> '{}')
          конец
         как редкость,
         считать(*)
    из магии.сеты,
         jsonb_array_elements(data->'booster') ускоритель
группировать по редкости
порядок по количеству desc;
 9СОВЕТ: Вы можете переместить функцию возврата набора в элемент LATERAL FROM.

jsonb_array_elements_text() — это функция , возвращающая набор , как может быть
видно здесь:

 > \df jsonb_array_elements_text
Список функций
─[ ЗАПИСЬ 1 ]───────┬──────────────────────────── ────
Схема │ pg_catalog
Имя │ jsonb_array_elements_text
Тип данных результата │ Текст SETOF
Типы данных аргумента │ from_json jsonb, текст значения OUT
Тип │ нормальный

Итак, я был довольно ленив при написании предыдущего SQL-запроса. Ленивый
достаточно для использования SRF в скалярном контексте внутри конструкции CASE . В
предыдущих версиях PostgreSQL, SQL-движок расширял выбор
вывод в столько строк, сколько было возвращено вызовом jsonb_array_elements() .

Поправим запрос для PostgreSQL 10 и очистим семантику, обработав
по одной строке за раз в нашем предложении проекции SELECT .

Проблема, которую мы хотим решить в этом запросе, связана с плохой структурой данных.
семантика в JSON-представлении набора данных, который мы получили от Magic
проект. Запись booster представляет собой либо строку JSON, либо массив JSON из
струны.

Чтобы иметь возможность обрабатывать эту информацию в SQL, нам всегда нужно иметь
одинаковые типы значений, поэтому нам нужно преобразовать данные в общий
представление. Здесь самым простым распространенным представлением является массив JSON из
строки, поэтому нам нужно преобразовать скалярные записи в массив, содержащий
одиночная запись. Затем мы можем вернуться к нашей предыдущей обработке, где мы
разложить массив благодаря специализированному jsonb_array_elements_text()
функция.

В SQL это преобразуется в следующий запрос:

 с бустером (rarity_js) как (
  выберите случай jsonb_typeof (бустер)
              когда "массив"
              затем бустер
              еще jsonb_build_array (бустер)
          конец
    из магии.сеты,
         jsonb_array_elements(data->'booster') как бустер
)
  выберите initcap (редкость) как редкость, количество (*)
    от усилителя,
         jsonb_array_elements_text(rarity_js) как t(редкость)
группировать по редкости
порядок по количеству desc;

В первой части запроса бустер Common Table Expression
нормализовать данные JSON, с которыми нам приходится иметь дело в data->’booster’ вложенных
JSON-документ. Мы снова находим нашу конструкцию CASE , и на этот раз вместо
используя семантику PostgreSQL 9.