Чем больше работаю с Postgres, тем больше радует меня эта СУБД. Шикарная работа с JSON, позволяющая хранить структуры разнотипных данных в полях JSON и JSONB, и даже вешать на них индексы для быстрого поиска внутри них - находка для архивирования твитов и им подобных. Отличный набор функций для обработки текста, включая regexp. Типы-массивы (тоже с индексами). Переменных-таблиц нет, но их с успехом заменяют переменные-массивы или временные таблицы. Вот некоторые выражения, приводящие меня в щенячий восторг:
-- Новая таблица по образцу выборки
CREATE TABLE new_table AS SELECT field1, field2 FROM old_table LIMIT 10;
-- Временная самоликвидирующаяся таблица
CREATE TEMP TABLE IF NOT EXISTS new_table(id INT, data JSON) ON COMMIT DROP;
-- Одно и то-же, во втором случае используется массив
SELECT * FROM old_table WHERE id IN (1,2,3);
SELECT * FROM old_table WHERE id = ANY('{1,2,3}');
-- Создание массива в процессе группировки
SELECT array_agg(text_id) FROM old_table WHERE id IN (1,2,3);
-- JSON-массив JSON-объектов
SELECT json_agg(t.jobj) FROM (
SELECT json_build_object('text_id', id, 'record_id', _id) jobj
FROM old_table
) t;
-- Cоотношение твитов с вложенными чувствами и черствых:
-- на выходе что-то вроде "{ "false" : 9115, "true" : 166 }"
SELECT json_object_agg(t.ps, t.c) FROM (
SELECT src#>>'{possibly_sensitive}' ps, COUNT(_id) c
FROM tweet_archive
WHERE src#>>'{possibly_sensitive}' = ANY('{false,true}') -- а тут массив с текстами
GROUP BY src#>>'{possibly_sensitive}'
) t;
json.dumps(tweet, separators=(',',':'), encoding='utf-8', ensure_ascii=False)
Еще одна возможность, которая может пригодиться - создание процедур в Postgres, используя Python. Думаю, примера будет достаточно, чтобы понять, как это делается (конечно официальной документации никто не отменял):
CREATE OR REPLACE FUNCTION outer_procedure(p_limit INT) RETURNS VOID AS $$
# Добавление пути для импорта нашего модуля
import sys
sys.path.insert(0, '/home/developer/plpython_func/')
# Загрузка модуля
import test_functions
# На время разработки, пока модуль будет меняться,
# его нужно насильно перезагружать, т.к. он кешируется
reload(test_functions)
# Собственно, запуск функции из модуля.
# plpy - это объект для взаимодействия с БД
test_functions.run(plpy, p_limit)
return None
$$ LANGUAGE plpythonu VOLATILE STRICT;
Внешняя процедура на Python (test_functions.py), которую мы загружаем:
# -*- coding: utf-8 -*-
import traceback
def run(plpy, limit):
# Готовим план выполнения
sql = 'INSERT INTO new_table(id, text) VALUES($1, $2)'
plan = plpy.prepare(sql, ["int", "text"])
# Выполняем вставку внутри транзакции, ловим ошибки
try:
with plpy.subtransaction():
plpy.execute(plan, [1, "text"])
except plpy.SPIError as e:
plpy.error("Error inside transaction: %s" % e.args)
except:
plpy.error(traceback.format_exc(10))
