# Postgres # Как использовать процедуры и функции на Pl/SQL, какие есть еще языки для работы с Postgres?
В PostgreSQL для написания процедур и функций используется процедурный язык **PL/pgSQL** (Procedural Language/PostgreSQL Structured Query Language).
#### Функции (Functions)
Функции в PostgreSQL могут возвращать значение (или набор строк) и могут использоваться в SQL-запросах (в секциях `SELECT`, `WHERE`, `JOIN`).
Синтаксис создания простой функции:
SQL
``` CREATE OR REPLACE FUNCTION calculate_bonus(emp_id INT, bonus_percentage NUMERIC) RETURNS NUMERIC AS $$ DECLARE current_salary NUMERIC; calculated_bonus NUMERIC; BEGIN -- Получаем текущую зарплату сотрудника SELECT salary INTO current_salary FROM employees WHERE id = emp_id; -- Если сотрудник не найден, возвращаем 0 IF current_salary IS NULL THEN RETURN 0; END IF; calculated_bonus := current_salary * (bonus_percentage / 100); RETURN calculated_bonus; END; $$ LANGUAGE plpgsql; ```
Вызов функции:
SQL
``` SELECT calculate_bonus(5, 10.5); ```
#### Процедуры (Procedures)
Процедуры появились начиная с PostgreSQL 11. В отличие от функций, они **не возвращают значение** через `RETURN` (но могут возвращать данные через параметры `INOUT` или `OUT`), однако они **поддерживают транзакции** (`COMMIT`, `ROLLBACK`), в то время как функции — нет.
Синтаксис создания процедуры:
SQL
``` CREATE OR REPLACE PROCEDURE transfer_funds( sender_account INT, receiver_account INT, amount NUMERIC ) LANGUAGE plpgsql AS $$ BEGIN -- Снимаем средства с одного счета UPDATE accounts SET balance = balance - amount WHERE id = sender_account; -- Зачисляем средства на другой счет UPDATE accounts SET balance = balance + amount WHERE id = receiver_account; -- Фиксируем транзакцию COMMIT; END; $$; ```
Вызов процедуры (используется команда `CALL`):
SQL
``` CALL transfer_funds(1, 2, 500.00); ```
#### Основные управляющие конструкции PL/pgSQL
-
**Условия (IF / THEN / ELSE):**
SQL
``` IF v_count > 10 THEN -- код ELSIF v_count > 0 THEN -- код ELSE -- код END IF; ``` -
**Циклы (LOOP, WHILE, FOR):**
SQL
``` -- Цикл с условием WHILE v_counter < 10 LOOP v_counter := v_counter + 1; END LOOP; -- Обход результатов запроса FOR r_row IN SELECT * FROM employees LOOP RAISE NOTICE 'Employee: %', r_row.name; END LOOP; ``` -
**Обработка ошибок (EXCEPTION):**
SQL
``` BEGIN -- рискованный код EXCEPTION WHEN division_by_zero THEN RAISE NOTICE 'Деление на ноль!'; WHEN OTHERS THEN RAISE NOTICE 'Произошла непредвиденная ошибка: %', SQLERRM; END; ```
Главное отличие при выборе между ними: если вам нужно делать выборку данных внутри `SELECT` — пишите **функцию**. Если вам нужно выполнить набор команд модификации данных с управлением транзакциями — используйте **процедуру**.
Какой именно сценарий или задачу вы хотите реализовать с помощью PL/pgSQL? Могу написать конкретный пример под ваши требования.
### Чем различаются языки PL/SQL у Oracle и Postgres?
Сравнение процедурных расширений Oracle (**PL/SQL**) и PostgreSQL (**PL/pgSQL**) сводится к сопоставлению зрелого коммерческого стандарта и мощного открытого аналога. Несмотря на внешнюю схожесть (оба языка вдохновлены Ada и используют блоки `DECLARE`, `BEGIN`, `EXCEPTION`), между ними есть глубокие архитектурные и синтаксические различия.
#### Основные различия
**Характеристика****Oracle (PL/SQL)****PostgreSQL (PL/pgSQL)**
**Интеграция с СУБД**Язык является **родным и доминирующим** для СУБД. На нем написаны даже многие внутренние системные пакеты.Язык — один из множества поддерживаемых (`plpython`, `plperl`, `pltcl`), хотя и самый популярный для написания логики в БД.
**Пакеты (Packages)****Есть.** Позволяют группировать типы, функции и процедуры, а также имеют состояние сессии (общие переменные внутри пакета).**Нет** в чистом виде (хотя можно эмулировать через схемы). Переменные уровня сессии требуют сторонних расширений или таблиц.
**Типы данных и коллекции**Огромный выбор встроенных сложных типов: `TABLE` (ассоциативные массивы), `VARRAY`, `NESTED TABLE`, продвинутая работа с записями (`ROWTYPE`).Коллекции ограничены базовыми массивами и таблицами типов (строки таблиц, `RECORD`), работа с ними менее гибкая.
**Обработка SQL (курсоры)**Поддерживает неявные курсоры с мощными атрибутами (`%FOUND`, `%NOTFOUND`, `%ROWCOUNT`), а также продвинутые конструкции `BULK COLLECT` и `FORALL` для массовой обработки (оптимизация контекст-переключений).Курсоры есть (`FOR` циклы по выборкам), но массовые операции (BULK) реализуются иначе, часто через конструкции вроде `ANY` или временные таблицы.
**Управление транзакциями**Поддерживает **автономные транзакции** (`PRAGMA AUTONOMOUS_TRANSACTION`), позволяя фиксировать изменения в логах независимо от основной транзакции.Обычные функции не могут управлять транзакциями. Процедуры могут выполнять `COMMIT` или `ROLLBACK`, но автономных транзакций «из коробки» до сих пор нет (требуются костыли через внешние вызовы или dblink).
**Обработка ошибок**Большой встроенный набор предопределенных исключений (`NO_DATA_FOUND`, `TOO_MANY_ROWS` и др.).Исключения обрабатываются через блок `EXCEPTION`, но кодов ошибок и имен меньше, часто приходится проверять коды ошибок через `SQLSTATE`.
**Объектно-ориентированные возможности**Поддерживает ООП-концепции: типы объектов с методами, наследование объектов в базе данных.Отсутствуют ООП-возможности на уровне языка процедур.
#### Примеры синтаксических отличий
**1. Объявление переменных и присваивание:**
-
**Oracle:** Часто используется оператор присваивания `:=`, но также активно применяется `DEFAULT` или прямая инициализация. Широко развита работа с атрибутами `%TYPE` и `%ROWTYPE`.
-
**PostgreSQL:** Синтаксис очень похож (`:=`), но синтаксис функций требует оборачивания тела в текстовые кавычки (`$$ ... $$`) и явного указания языка `LANGUAGE plpgsql`.
**2. Функция выборки одной строки (когда строка не найдена):**
-
**Oracle:** Если запрос в блоке PL/SQL возвращает 0 строк, автоматически возбуждается исключение `NO_DATA_FOUND`.
-
**PostgreSQL:** При использовании `SELECT ... INTO` в PL/pgSQL переменные просто становятся `NULL`, исключение не генерируется (если не настроено строгое поведение).
**3. Создание пакетов (отсутствие в Postgres):**
-
**Oracle:**
SQL
``` CREATE OR REPLACE PACKAGE my_pack AS procedure do_something; END my_pack; ``` -
**PostgreSQL:** Пакеты создать нельзя. Функции и процедуры создаются по отдельности, а для изоляции и структурирования кода используются разные **схемы** (schemas).
Какой конкретный паттерн из Oracle вы пытаетесь перенести в PostgreSQL? Возможно, для него есть стандартный и элегантный обходной путь.
### Какие еще языки могут использоваться с postgres?
PostgreSQL обладает уникальной архитектурой расширяемости (благодаря системе обработчиков языков — *Language Handlers*), которая позволяет писать функции, триггеры и процедуры не только на PL/pgSQL, но и на множестве других языков программирования.
Все языки делятся на две категории: входящие в стандартный комплект и сторонние расширения.
#### 1. Языки из стандартного дистрибутива
Эти языки поставляются вместе с базовым сервером PostgreSQL, их нужно только активировать в конкретной базе данных командой `CREATE EXTENSION extension_name;` (например, `CREATE EXTENSION plpython3u;`).
-
**PL/Python (`plpython3u`)**
Один из самых популярных альтернативных языков. Позволяет использовать всю мощь Python для работы с данными, математикой, парсинга или вызова внешних API прямо из базы.
SQL
``` CREATE FUNCTION get_max(a int, b int) RETURNS int AS $$ return max(a, b) $$ LANGUAGE plpython3u; ```
-
**PL/Perl (`plperl`)** Исторически один из первых языков в Postgres. Удобен для сложных манипуляций со строками, регулярных выражений и задач системного администрирования.
-
**PL/Tcl (`pltcl`)** Язык Tcl (Tool Command Language), исторически встроенный в экосистему PostgreSQL. Реже используется в новых проектах, но поддерживается «из коробки».
#### 2. Сторонние и популярные языки (расширения)
Эти языки разрабатываются сторонним сообществом, устанавливаются отдельно, но глубоко интегрируются в СУБД:
-
**PL/v8 (JavaScript / V8)**
Позволяет писать функции на **JavaScript**, используя движок V8 от Google. Идеально подходит, если нужно обрабатывать JSON-документы (которых в Postgres множество) с помощью привычного JS-кода или переиспользовать бизнес-логику из Node.js/фронтенда на стороне базы данных.
-
**PL/Java**
Дает возможность писать код на **Java**, используя стандартные интерфейсы JDBC. Востребовано в энтерпрайз-среде, где вся логика монолита завязана на Java.
-
**PL/R**
Интеграция со статистическим языком **R**. Позволяет выполнять сложные математические вычисления, статистический анализ и строить модели машинного обучения прямо внутри SQL-запросов.
-
**PL/Lua**
Сверхбыстрый и легкий скриптовый язык **Lua**, встроенный в PostgreSQL. Часто применяется там, где критична максимальная производительность скриптов в базе.
-
**PL/sh**
Позволяет выполнять **системные shell-скрипты** (Bash и др.) прямо из базы данных (например, запускать утилиты ОС по триггеру). Это крайне опасный с точки зрения безопасности язык, поэтому он используется только в изолированных контурах.
#### Когда и что использовать?
-
Для стандартной бизнес-логики, CRUD-операций и высокой скорости работы без лишних зависимоств выбирайте **PL/pgSQL**.
-
Для работы с нейросетями, сложной аналитики, парсинга или интеграции с внешними библиотеками экосистемы Python незаменим **PL/Python**.
-
Для интенсивной работы с JSON и быстрой обработки данных через знакомый синтаксис — **PL/v8 (JS)**.
# Как активировать PL/Python, примеры функции и процедуры
Чтобы использовать **PL/Python**, необходимо, чтобы модуль Python был установлен в вашей операционной системе для вашей версии PostgreSQL (например, пакет `postgresql-plpython3` в Linux), после чего его можно включить в нужной базе данных.
#### 1. Активация PL/Python
Активировать расширение нужно в целевой базе данных от имени суперпользователя (`postgres`):
SQL
``` CREATE EXTENSION IF NOT EXISTS plpython3u; ```
>
**Важная деталь:** Буква `u` на конце расширения означает *untrusted* (недоверенный). Это значит, что функции на PL/Python могут обращаться к файловой системе сервера, сети и операционной системе, поэтому создавать функции на PL/Python может только суперпользователь СУБД.
#### 2. Пример функции (Function)
Функции в PostgreSQL возвращают значение. В PL/Python вы можете импортировать любые сторонние библиотеки Python (например, `json`, `math`, `requests`, `pandas` и др.).
Пример функции для парсинга и валидации JSON-строки с расчетом итоговой стоимости:
SQL
``` CREATE OR REPLACE FUNCTION calculate_cart_total(cart_json text) RETURNS numeric AS $$ import json try: data = json.loads(cart_json) items = data.get("items", []) total = 0.0 for item in items: price = item.get("price", 0) quantity = item.get("quantity", 1) total += price * quantity return round(total, 2) except Exception as e: # raise_notice или log позволяют отправлять сообщения в клиент/лог базы plpy.notice(f"Ошибка при обработке JSON: {e}") return 0.0 $$ LANGUAGE plpython3u; ```
**Вызов функции:**
SQL
``` SELECT calculate_cart_total('{"items": [{"price": 100.5, "quantity": 2}, {"price": 50.0, "quantity": 1}]}'); -- Вернет: 251.00 ```
#### 3. Пример процедуры (Procedure)
Процедуры в PostgreSQL не возвращают результат и поддерживают управление транзакциями (`plpy.commit()`, `plpy.rollback()`).
Специальный модуль `plpy` внутри PL/Python позволяет выполнять SQL-запросы внутри базы.
Пример процедуры, которая находит устаревшие записи, отправляет их во внешнюю систему (имитация) и удаляет их:
SQL
``` CREATE OR REPLACE PROCEDURE cleanup_old_logs(days_old int) AS $$ import time # Выполняем SQL-запрос к БД с помощью plpy.execute plan = plpy.prepare( "SELECT id, message FROM system_logs WHERE created_at < NOW() - ($1 || ' days')::INTERVAL", ["int"] ) result = plpy.execute(plan, [days_old]) plpy.info(f"Найдено записей для удаления: {len(result)}") if len(result) > 0: # Логика обработки записей на Python for row in result: log_id = row["id"] # Здесь можно вызвать внешний API, сохранить резервную копию и т.д. # Удаление записей delete_plan = plpy.prepare( "DELETE FROM system_logs WHERE created_at < NOW() - ($1 || ' days')::INTERVAL", ["int"] ) plpy.execute(delete_plan, [days_old]) # Фиксация изменений plpy.commit() $$ LANGUAGE plpython3u; ```
**Вызов процедуры:**
SQL
``` CALL cleanup_old_logs(30); ```
#### Дополнительные возможности `plpy`
Внутри PL/Python вам автоматически доступен встроенный объект `plpy`:
-
`plpy.execute("SELECT ...")` — выполнение произвольного SQL-запроса.
-
`plpy.prepare(query, [types])` — подготовка SQL-запроса для многократного выполнения.
-
`plpy.notice(msg)`, `plpy.info(msg)`, `plpy.warning(msg)` — вывод сообщений пользователю.
-
`plpy.error(msg)` — генерация исключения, отменяющего транзакцию.
### Возвращают ли функции PL/Python наборы строк (recordsets) которые можно использовать в SQL-запросах?
**Да, функции PL/Python могут возвращать наборы строк.** Их можно использовать непосредственно в блоке `FROM` SQL-запроса, как обычную таблицу или представление (View).
Возвращать наборы данных из PL/Python можно двумя основными способами:
1.
**Список словарей / списков / кортежей** — если функция формирует и отдает все данные сразу.
2.
**Итератор / Генератор (`yield`)** — если данных много и их нужно отдавать по одной строке (ленивые вычисления), чтобы не перегружать оперативную память.
#### Вариант 1. Возврат всех строк через список (List of Dicts)
Для возврата таблицы используется конструкция `RETURNS TABLE(...)` или `RETURNS SETOF record_type`. Внутри Python-кода вы возвращаете обычный список Python-словарей, где ключи соответствуют именам колонок.
SQL
``` CREATE OR REPLACE FUNCTION generate_user_report(min_age int) RETURNS TABLE(user_id int, full_name text, status text) AS $$ # В реальности данные могут запрашиваться из внешнего API или рассчитываться users = [ {"user_id": 1, "full_name": "Алексей Иванов", "age": 25}, {"user_id": 2, "full_name": "Мария Сидорова", "age": 19}, {"user_id": 3, "full_name": "Иван Петров", "age": 34} ] result = [] for user in users: if user["age"] >= min_age: result.append({ "user_id": user["user_id"], "full_name": user["full_name"], "status": "Adult" if user["age"] >= 21 else "Young" }) return result $$ LANGUAGE plpython3u; ```
##### Использование функции в SQL:
SQL
``` SELECT * FROM generate_user_report(20) WHERE status = 'Adult'; ```
#### Вариант 2. Построчная отдача через генератор (`yield`)
Если функция должна выгрузить 100 000+ строк из внешнего ресурса, сохранение их всех в список в памяти вызовет высокую нагрузку на RAM. В PL/Python можно использовать встроенный оператор Python `yield`.
SQL
``` CREATE OR REPLACE FUNCTION stream_numbers(limit_num int) RETURNS TABLE(num int, square int) AS $$ for i in range(1, limit_num + 1): # Отдаем по одной строке за раз yield {"num": i, "square": i * i} $$ LANGUAGE plpython3u; ```
##### Использование в SQL-запросе:
SQL
``` SELECT num, square FROM stream_numbers(5) WHERE square > 10; ```
**Результат:**
**num****square**
416
525
#### Вариант 3. Возврат результата встроенного SQL-запроса (`plpy.execute`)
Если функция выполняет внутренний SQL-запрос с помощью `plpy.execute()`, возвращаемый объект `PLPyResult` уже является итерируемым набором строк, поэтому его можно вернуть напрямую:
SQL
``` CREATE OR REPLACE FUNCTION get_active_users() RETURNS TABLE(id int, email text) AS $$ # plpy.execute возвращает результат, который совместим с SETOF/TABLE res = plpy.execute("SELECT id, email FROM users WHERE is_active = true") return res $$ LANGUAGE plpython3u; ```
# Использование Postgres в C# # Как вызываются процедуры и функции на PL/Python из C#?
Вызов процедур и функций PostgreSQL из C# (начиная с C# 7 и выше) выполняется через стандартную библиотеку **Npgsql** (официальный ADO.NET провайдер для PostgreSQL).
С точки зрения приложения C# **абсолютно не имеет значения, на чем написана функция/процедура** (на PL/pgSQL, PL/Python, PL/v8 или C). СУБД предоставляет единый интерфейс вызова.
#### Вызов функции, возвращающей значение или набор строк
Для вызова функций рекомендуется использовать обычные SQL-запросы `SELECT * FROM function_name(@param)`. Это универсальный подход, работающий как для скалярных значений, так и для табличных результатов (`RETURNS TABLE`).
##### Пример 1: Вызов функции, возвращающей набор строк (`RETURNS TABLE`)
Предположим, у нас есть функция PL/Python из предыдущего примера:
**SQL**
``` CREATE OR REPLACE FUNCTION generate_user_report(min_age int) RETURNS TABLE(user_id int, full_name text, status text) AS $$ # PL/Python логика $$ LANGUAGE plpython3u; ```
**Код C# для вызова функции и чтения данных:**
``` using System; using System.Threading.Tasks; using Npgsql; public class PostgresExample { private const string ConnectionString = "Host=localhost;Database=mydb;Username=postgres;Password=secret"; public async Task ReadUserReportAsync(int minAge) { await using var conn = new NpgsqlConnection(ConnectionString); await conn.OpenAsync(); // Обращаемся к функции как к обычной таблице const string sql = "SELECT user_id, full_name, status FROM generate_user_report(@minAge)"; await using var cmd = new NpgsqlCommand(sql, conn); cmd.Parameters.AddWithValue("minAge", minAge); await using var reader = await cmd.ExecuteReaderAsync(); while (await reader.ReadAsync()) { // Используем кортежи (Tuples), доступные начиная с C# 7 var (userId, fullName, status) = ( reader.GetInt32(0), reader.GetString(1), reader.GetString(2) ); Console.WriteLine($"ID: {userId}, Имя: {fullName}, Статус: {status}"); } } } ```
##### Пример 2: Вызов скалярной функции (возвращает одно значение)
C#
``` public async Task GetCartTotalAsync(string cartJson) { await using var conn = new NpgsqlConnection(ConnectionString); await conn.OpenAsync(); const string sql = "SELECT calculate_cart_total(@cartJson)"; await using var cmd = new NpgsqlCommand(sql, conn); cmd.Parameters.AddWithValue("cartJson", cartJson); // ExecuteScalarAsync возвращает первое значение первой строки var result = await cmd.ExecuteScalarAsync(); return Convert.ToDecimal(result); } ```
#### Вызов процедуры (Procedure)
Процедуры в PostgreSQL вызывают с помощью команды `CALL`. В Npgsql это можно сделать двумя способами: через явный `CALL` или через `CommandType.StoredProcedure`.
##### Пример вызова процедуры через `CALL`
Предположим, у нас есть процедура PL/Python:
**SQL**
``` CREATE OR REPLACE PROCEDURE cleanup_old_logs(days_old int) AS $$ # PL/Python логика $$ LANGUAGE plpython3u; ```
**Код C#:**
``` public async Task CleanupLogsAsync(int daysOld) { await using var conn = new NpgsqlConnection(ConnectionString); await conn.OpenAsync(); // Явный вызов процедуры const string sql = "CALL cleanup_old_logs(@daysOld)"; await using var cmd = new NpgsqlCommand(sql, conn); cmd.Parameters.AddWithValue("daysOld", daysOld); // Используем ExecuteNonQueryAsync, так как процедура не возвращает табличный результат await cmd.ExecuteNonQueryAsync(); } ```
#### Особенности работы с массивами (ARRAY) из C# 7
Если ваша функция PL/Python принимает или возвращает массив PostgreSQL (например, `integer[]` или `text[]`), Npgsql автоматически сопоставляет их с массивами C# (`int[]`, `string[]`).
**Пример:**
``` public async Task ProcessIdsAsync(int[] ids) { await using var conn = new NpgsqlConnection(ConnectionString); await conn.OpenAsync(); const string sql = "SELECT process_array_function(@ids)"; await using var cmd = new NpgsqlCommand(sql, conn); // Npgsql автоматически приведёт int[] C# к integer[] в Postgres cmd.Parameters.AddWithValue("ids", ids); await cmd.ExecuteNonQueryAsync(); } ```
# Как передавать и парсить JSON/JSONB из C# в PL/Python функцию через Npgsql?
Передача данных в формате **JSON / JSONB** из C# в PL/Python — один из наиболее эффективных подходов: C# сериализует объект в JSON, Npgsql передает его в PostgreSQL как native-тип `jsonb`, а PL/Python автоматически или с помощью стандартного модуля `json` превращает его в родной словарь (`dict`) или список (`list`).
#### 1. Подготовка функции в PostgreSQL (PL/Python)
Функция принимает тип `jsonb` (или `json`). В PL/Python аргументы типа `json/jsonb` передаются в виде обычных **строк**, поэтому внутри их нужно распарсить с помощью стандартного модуля `json`.
SQL
``` CREATE OR REPLACE FUNCTION process_user_data(payload jsonb) RETURNS TABLE(user_id int, status text, is_valid boolean) AS $$ import json # PL/Python получает json/jsonb как строку data = json.loads(payload) users = data.get("users", []) result = [] for user in users: u_id = user.get("id") email = user.get("email", "") # Простая проверка/обработка is_valid = "@" in email status = "Active" if user.get("age", 0) >= 18 else "Pending" result.append({ "user_id": u_id, "status": status, "is_valid": is_valid }) return result $$ LANGUAGE plpython3u; ```
#### 2. Вызов из C# с помощью Npgsql и System.Text.Json
В современных версиях Npgsql передача JSON выполняется через встроенную поддержку `System.Text.Json` или явное указание типа `NpgsqlDbType.Jsonb`.
##### Вариант 1: Использование `System.Text.Json` (рекомендуемый)
C#
``` using System; using System.Collections.Generic; using System.Text.Json; using System.Threading.Tasks; using Npgsql; using NpgsqlTypes; public class JsonPostgresExample { private const string ConnectionString = "Host=localhost;Database=mydb;Username=postgres;Password=secret"; public async Task ProcessDataAsync() { // 1. Формируем C# объект для передачи var requestData = new { users = new[] { new { id = 101, email = "alex@example.com", age = 25 }, new { id = 102, email = "invalid_email", age = 16 } } }; // 2. Сериализуем объект в строку JSON string jsonPayload = JsonSerializer.Serialize(requestData); await using var conn = new NpgsqlConnection(ConnectionString); await conn.OpenAsync(); const string sql = "SELECT user_id, status, is_valid FROM process_user_data(@payload)"; await using var cmd = new NpgsqlCommand(sql, conn); // 3. Добавляем параметр с явным указанием NpgsqlDbType.Jsonb cmd.Parameters.Add(new NpgsqlParameter("payload", NpgsqlDbType.Jsonb) { Value = jsonPayload }); // 4. Читаем результат await using var reader = await cmd.ExecuteReaderAsync(); while (await reader.ReadAsync()) { var userId = reader.GetInt32(0); var status = reader.GetString(1); var isValid = reader.GetBoolean(2); Console.WriteLine($"ID: {userId} | Статус: {status} | Валиден: {isValid}"); } } } ```
#### 3. Обратный сценарий: Возврат JSONB из PL/Python в C#
Если функция PL/Python должна вернуть сложный JSON-объект обратно в C#:
##### Функция в PostgreSQL:
SQL
``` CREATE OR REPLACE FUNCTION get_system_stats() RETURNS jsonb AS $$ import json stats = { "server_time": "2026-09-12", "metrics": { "cpu_usage": 42.5, "active_connections": 12 }, "tags": ["prod", "primary"] } # Возвращаем JSON-строку return json.dumps(stats) $$ LANGUAGE plpython3u; ```
##### Код чтения в C#:
C#
``` public async Task GetStatsAsync() { await using var conn = new NpgsqlConnection(ConnectionString); await conn.OpenAsync(); const string sql = "SELECT get_system_stats()"; await using var cmd = new NpgsqlCommand(sql, conn); var rawJson = (string)await cmd.ExecuteScalarAsync(); // Десериализуем JSON в C# объект using var doc = JsonDocument.Parse(rawJson); var cpu = doc.RootElement .GetProperty("metrics") .GetProperty("cpu_usage") .GetDouble(); Console.WriteLine($"CPU Usage: {cpu}%"); } ```
#### Ключевые моменты при работе с JSON/JSONB:
1.
**Тип параметра в C#:** При передаче строкового JSON всегда указывайте `NpgsqlDbType.Jsonb` (или `NpgsqlDbType.Json`), иначе Npgsql передаст строку как обычный `text`, и Postgres выдаст ошибку несоответствия типов.
2.
**Сериализация внутри PL/Python:**
-
При **приеме** `jsonb` в Python аргумент является строкой `str` — применяйте `json.loads(arg)`.
-
При **возврате** `jsonb` отправляйте строку — применяйте `json.dumps(obj)`.
# Какой тип даных поля в таблице Postgres лучше использовать для хранения пакетов json, но там могут быть и другие типы, например XML или изображения?
Для такой задачи **не подходит** чистый тип `JSON` или `JSONB`, так как PostgreSQL начнет проверять валидность JSON-структуры и выдаст ошибку при попытке сохранить XML или бинарные данные (изображение).
Наилучшим решением будет использование одного из следующих подходов в зависимости от ваших приоритетов по размеру и производительности.
#### Подход 1: `BYTEA` (Бинарные данные) — *Самый универсальный и производительный*
Тип `BYTEA` (byte array) хранит данные «как есть» в виде сырого массива байт. Ему абсолютно неважно, что именно там находится — JSON, XML, PNG/JPEG или текстовый файл.
SQL
``` CREATE TABLE payload_store ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, content_type VARCHAR(50) NOT NULL, -- 'application/json', 'application/xml', 'image/png' payload BYTEA NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() ); ```
-
**Плюсы:**
-
Абсолютная универсальность (принимает всё: от текстовых форматов до бинарных изображений).
-
Минимальные накладные расходы при записи/чтении (нет перекодировок в текст).
-
Корректно сохраняет изображения без раздувания размера.
-
**Минусы:**
-
Из C# нужно передавать данные как `byte[]` (для JSON/XML это будет байтовый массив строки UTF-8).
-
Внутри PostgreSQL нельзя делать прямое обращение к полям JSON (например, `payload->>'name'`).
#### Подход 2: `TEXT` — *Удобнее для отладки, но со штрафом для бинарных данных*
Если вы хотите видеть содержимое пакетов (JSON и XML) напрямую через консоль или клиенты (DBeaver, pgAdmin) без декодирования байтов, можно использовать `TEXT`.
SQL
``` CREATE TABLE payload_store ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, content_type VARCHAR(50) NOT NULL, payload TEXT NOT NULL ); ```
-
**Плюсы:**
-
Текстовые данные (JSON, XML) хранятся в читаемом виде.
-
Текст можно при необходимости привести к типу `JSONB` налету: `SELECT (payload::jsonb)->>'key' WHERE content_type = 'application/json'`.
-
**Минусы:**
-
**Изображения придется кодировать в Base64** перед сохранением из C#.
-
Кодирование в Base64 увеличивает объем хранимого файла (изображения) примерно на **33%**.
#### Сравнение вариантов
**Критерий****BYTEA****TEXT (с Base64 для фото)**
**Универсальность**100% (любые бинарники и текст)100% (но изображения нужно кодировать)
**Размер изображений**Исходный (1:1)+33% к размеру файла из-за Base64
**Удобство просмотра JSON/XML в СУБД**Требуется `encode(payload, 'escape')`Читается из «коробки»
**Сложность в C# (Npgsql)**Работа через `byte[]`Работа через `string`
#### Рекомендация
1.
Если **изображений много или они большие** — однозначно используйте **`BYTEA`**.
2.
Если хранение изображений — редкий случай, а **JSON/XML нужно часто просматривать напрямую в БД** — используйте **`TEXT`** (сохраняя картинки как Base64-строки).
3.
**Обязательно добавьте колонку `content_type`** (MIME-тип) или `payload_kind` — без нее приложение на C# не сможет понять, как правильно интерпретировать вычитанные данные (как раскодировать JSON/XML или как отдать картинку).
##### Как работать с `BYTEA` из C# (Npgsql):
C#
``` // Для JSON/XML кодируем строку в UTF-8 байты: byte[] jsonBytes = System.Text.Encoding.UTF8.GetBytes(jsonString); // Для изображений берем байты файла напрямую: byte[] imageBytes = await System.IO.File.ReadAllBytesAsync("photo.png"); // Передача в NpgsqlParameter: cmd.Parameters.Add(new NpgsqlParameter("payload", NpgsqlTypes.NpgsqlDbType.Bytea) { Value = imageBytes }); ```
# Запрос данных из пакетов JSON в поле таблицы Для хранения пакетов используем такую таблицу: [![upload_data.png](https://docs.consulting-plus.com/uploads/images/gallery/2026-09/scaled-1680-/upload-data.png)](https://docs.consulting-plus.com/uploads/images/gallery/2026-09/upload-data.png) Пакеты хранятся в поле pack\_data, они одинаковой структуры для определенного типа записи (поле type\_id) Пакет имеет такую структуру: ```json [ {"charsetinfo": {"name": "utf-8"} }, {"history": [ {"BOARDID": "TQBR", "TRADEDATE": "2026-09-21", "SHORTNAME": "РоссЮг ао", "SECID": "MRKY", "NUMTRADES": 275, "VALUE": 1541379, "OPEN": 0.07065, "LOW": 0.0695, "HIGH": 0.0716, "LEGALCLOSEPRICE": 0.07015, "WAPRICE": 0.0704, "CLOSE": 0.07055, "VOLUME": 21890000, "MARKETPRICE2": 0.0702, "MARKETPRICE3": 0.0702, "ADMITTEDQUOTE": null, "MP2VALTRD": 766668, "MARKETPRICE3TRADESVALUE": 766668, "ADMITTEDVALUE": null, "WAVAL": 0, "TRADINGSESSION": 3, "CURRENCYID": "SUR", "TRENDCLSPR": 0.21, "TRADE_SESSION_DATE": "2026-09-21"},{"BOARDID": "TQBR", "TRADEDATE": "2026-09-21", "SHORTNAME": "Мостотрест", "SECID": "MSTT", "NUMTRADES": 162, "VALUE": 1539552, "OPEN": 69.4, "LOW": 67.9, "HIGH": 69.4, "LEGALCLOSEPRICE": 67.9, "WAPRICE": 68.5, "CLOSE": 68.35, "VOLUME": 22470, "MARKETPRICE2": 68.45, "MARKETPRICE3": 68.45, "ADMITTEDQUOTE": null, "MP2VALTRD": 1292162.5, "MARKETPRICE3TRADESVALUE": 1292162.5, "ADMITTEDVALUE": null, "WAVAL": 0, "TRADINGSESSION": 3, "CURRENCYID": "SUR", "TRENDCLSPR": -1.51, "TRADE_SESSION_DATE": "2026-09-21"}, ... ] } ] ``` Нам нужно соединить все пакеты и получить записи из поля "history". Чтобы развернуть массив из массива `history` во всех JSON-пакетах одной SQL-выборкой, в PostgreSQL используются функции для работы с JSON/JSONB:
1. **Приведение типа:** Так как поле `pack_data` имеет тип `text`, его нужно привести к `jsonb` (или `json`): `pack_data::jsonb`. 2. **`jsonb_array_elements`:** Позволяет «расплющить» (распаковать) элементы JSON-массива в отдельные строки.
#### SQL-запрос Поскольку yаш JSON представляет собой массив объектов, где массив `history` находится во **втором элементе** (`pack_data::jsonb -> 1 -> 'history'`), запрос будет выглядеть так:
SQL
```sql SELECT u.upload_id, u.pack_name, u.upload_time, -- Извлекаем поля из объектов внутри history history_item ->> 'SECID' AS secid, history_item ->> 'SHORTNAME' AS shortname, history_item ->> 'TRADEDATE' AS tradedate, (history_item ->> 'OPEN')::numeric AS open_price, (history_item ->> 'CLOSE')::numeric AS close_price, (history_item ->> 'VOLUME')::bigint AS volume, (history_item ->> 'VALUE')::numeric AS value, history_item ->> 'BOARDID' AS boardid FROM trading.upload_data u WHERE u.type_id = 1 -- 1. Извлекаем массив history из второго элемента корня JSON и разворачиваем его по строкам CROSS JOIN LATERAL jsonb_array_elements(u.pack_data::jsonb -> 1 -> 'history') AS history_item; ```
#### Как это работает:
1. **`u.pack_data::jsonb -> 1`** — берет второй элемент корневого JSON-массива (индексация с 0, поэтому `1` указывает на объект `{"history": [...]}`). 2. **`-> 'history'`** — переходит непосредственно к массиву цен и сделок. 3. **`jsonb_array_elements(...)`** — превращает каждый элемент массива `history` в отдельную строку результата. 4. **`CROSS JOIN LATERAL`** — связывает каждую строку из таблицы `upload_data` с каждым распакованным элементами из массива `history`. 5. **`history_item ->> 'FIELD_NAME'`** — извлекает текстовое значение конкретного ключа из JSON-объекта. 6. **`::numeric`, `::bigint`** — сразу приводит текстовые значения JSON к правильным математическим типам PostgreSQL для последующей фильтрации или агрегации.
#### Альтернатива: Если порядок элементов в корневом JSON может меняться Если элемент `history` не всегда гарантированно идет вторым (с индексом `1`), безопаснее сначала распаковать корневой массив, а затем найти элемент с ключом `history`:
SQL
```sql SELECT u.upload_id, history_item ->> 'SECID' AS secid, history_item ->> 'SHORTNAME' AS shortname, (history_item ->> 'CLOSE')::numeric AS close_price FROM trading.upload_data u WHERE u.type_id = 1 -- Распаковываем корневой массив CROSS JOIN LATERAL jsonb_array_elements(u.pack_data::jsonb) AS root_elem -- Распаковываем массив history (игнорируя элементы без этого ключа, например charsetinfo) CROSS JOIN LATERAL jsonb_array_elements(root_elem -> 'history') AS history_item; ```
Этот вариант гибче, так как найдет массив `history` в любом месте структуры пакета. ### Что делает оператор: ->>
Оператор **`->>`** в PostgreSQL используется для **извлечения значения из JSON или JSONB в виде обычного текста (`text`)**.
В PostgreSQL есть два похожих оператора для работы с JSON, и разница между ними принципиальна:
**Оператор****Что делает****Возвращаемый тип****Назначение**
**`->`**Извлекает поле или элемент массива**`json`** или **`jsonb`**Для дальнейшего навигационного продвижения вглубь структуры JSON
**`->>`**Извлекает поле или элемент массива**`text`**Для вывода конечных данных, фильтрации в `WHERE` или приведения типов
#### Примеры применения
Предположим, у вас есть переменная или колонка `data` с содержимым `{"user": {"name": "Иван", "age": 25}}`.
##### 1. Извлечение поля по ключу
SQL
``` -- Оператор -> возвращает JSON ("Иван" в кавычках) SELECT data -> 'user' -> 'name'; -- Результат: "Иван" (тип jsonb) -- Оператор ->> возвращает чистый текст (без кавычек) SELECT data -> 'user' ->> 'name'; -- Результат: Иван (тип text) ```
##### 2. Извлечение элемента из JSON-массива по индексу
Если в колонке находится массив `["apple", "banana", "cherry"]`:
SQL
``` SELECT data ->> 0; -- Результат: apple (тип text) ```
##### 3. Приведение типов для расчетов и фильтрации
Поскольку `->>` возвращает обычный `text`, результат можно легко привести к числу, дате или логическому типу через `::type`:
SQL
``` -- Извлекаем возраст как число для условия в WHERE SELECT * FROM users_table WHERE (data ->> 'age')::int >= 18; -- Извлекаем дату и делаем сортировку SELECT data ->> 'TRADEDATE' AS trade_date FROM trading.upload_data ORDER BY (data ->> 'TRADEDATE')::date DESC; ```
#### Чек-лист для запоминания
-
Если вы хотите **продолжить цепочку** погружения в JSON дальше — используйте одиночную стрелку: `data -> 'a' -> 'b'`.
-
На **самом последнем шаге**, когда нужно получить конечное значение в виде текста для вывода, используйте двойную стрелку: `data -> 'a' ->> 'b'`.
# Работа с Postgres SQL # Переключение текущей сессии в нужную схему. Допустим вам нужно сделать схему **main** текущей. Это достигается выполнением следующей команды: ```postgresql SET search_path TO main, public; ``` > **Как это работает:** Любые команды `CREATE TABLE qrtz_...` без явного указания схемы теперь будут создавать таблицы строго внутри `main`. #### Проверьте переключение Чтобы убедиться, что контекст изменился на `main`, выполните:
``` SHOW search_path; SELECT current_schema(); ```
*В ответе должно быть `main`.*
Указание двух схем определяет **порядок поиска объектов и резервный контекст**.
Когда вы пишете `SET search_path TO main, public;`, вы задаете списку схем приоритет слева направо:
1.
**При создании объектов (`CREATE TABLE ...`)**
PostgreSQL всегда создает новый объект в **первой** схеме списка (в данном случае `main`), если вы не указали схему явно (например, `CREATE TABLE public.my_table`).
2.
**При поиске и чтении данных (`SELECT * FROM my_table`)**
PostgreSQL ищет таблицу по очереди:
-
Сначала проверяет, есть ли `my_table` в схеме `main`.
-
Если не находит — ищет её в схеме `public`.
-
Если её нет и там — выдает ошибку `relation "my_table" does not exist`.
3.
**Доступ к системным функциям и расширениям**
В схеме `public` часто располагаются стандартные расширения (extensions), служебные функции или общие справочники базы данных. Указав `public` вторым номером, вы гарантируете, что PostgreSQL сможет найти эти системные функции, даже если вы работаете в `main`.
##### Что будет, если указать только `main`?
Если написать `SET search_path TO main;`:
-
Все таблицы создадутся в `main` (как и нужно).
-
Но если вашей программе или функции потребуется объект или расширение из `public`, PostgreSQL его не увидит и выдаст ошибку.
Поэтому запись `SET search_path TO main, public;` является стандартом: **новые объекты создаются в `main`, но старые и системные объекты из `public` остаются доступны для чтения**.