С Партишенс разобрался "с пол-пинка", всё работает отлично. До тех пор, пока я не захотел сделать создание таблиц динамически. То есть, я не хочу заранее создавать таблицы "впрок", а чтобы это делалось внутри триггера вставки данных. И вот тут наступил на грабли... Либо я что-то "не так" делаю, либо такая возможность не особо предусматривалась?
Итак. Есть "базовая" таблица, для неё делаю триггер вставки данных. Внутри триггера определяю вставку данных в другую таблицу. Особенность в том, что имя таблицы определяется так:
uniq_name=date_part( 'year', NEW.date_) || '_w' || date_part( 'week', NEW.date_); new_table := 'basket.block_181_' || uniq_name;
Далее я уже не могу сделать простой INSERT, потому что в нем нет возможности указать переменную в имени таблицы (или я вот тут как раз не прав?). Поэтому я формирую запрос в виде строки,
request := 'insert into ' || new_table || ' values ' || NEW;
которую потом передаю в EXECUTE
BEGIN EXECUTE request; EXCEPTION -- если таблица с нужным именем не определена, то надо её создать! WHEN undefined_table THEN begin -- создаем таблицу и нужные индексы для нее EXECUTE 'create table ' || new_table || '() inherits (block_181)'; EXECUTE 'CREATE INDEX date_181_' || uniq_name || ' ON ' || new_table || ' USING btree (date_ , omc , bsc , h , pcm )'; ....
Как видно по коду, если таблицы нету, то я её создаю (перехватываю EXCEPTION), а также создаю все нужные индексы - эта часть кода не показана, т.к. она не проблемная, тут вопросов нет. Основная (и пока единственная) проблема сидит в строке
EXECUTE request;
Проблема проявляется в случае, если во вставляемой строке есть дата или строки. Даты не обрамляются кавычками, а строки оказываются в двойных кавычках!!! То есть NEW (при вставке в переменную requets) возвращает такую строку:
(19,"Family name and name",100,2012-04-05)
Чёрт с ним, кавычки я могу заменить на одинарные, но что делать с датой? Вот тут-то как раз и "собака порылась"! Постгрес выдает ошибки, что я пытаюсь вставить числовые данные туда, где должна быть дата. И она прав, т.к. итоговая команда вставки, передаваемая в EXECUTE, выглядит примерно так (это не для таблицы block_181, а для другой):
INSERT INTO test_2012_w14 VALUES (19,"Family name and name",100,2012-04-05)
Я нашел 2 варианта решения проблемы:
1. Создаю временную таблицу, куда записываю данные (БЕЗ EXECUTE), а затем формирую (динамическую) команду перезаписи строки из этой временной таблицы в постоянную. Тут не фигурируют определенные данные, поэтому проблем не возникает.
Временную таблицу затем удаляю.
CREATE TABLE block_181_tmp (LIKE block_181); INSERT INTO block_181_tmp VALUES (new.*); EXECUTE 'insert into ' || new_table || ' select * from block_181_tmp'; DROP TABLE block_181_tmp;
2. Формирую полную команду вставки данных, с учетом всех данных.
request := 'insert into ' || new_table || ' ( date_, h, omc, bsc, pcm, ts, TIME_A_CHANNEL_BUSY, NB_A_CHANNEL_ALLOC ) values (' || '''' || NEW.date_ || ''', ' || NEW.h || ', ' || NEW.omc || ', ' || NEW.bsc || ', ' || NEW.pcm || ', ' || NEW.ts || ', ' || NEW.TIME_A_CHANNEL_BUSY || ', ' || NEW.NB_A_CHANNEL_ALLOC || ')';
Оба решения мне не нравятся, особенно второе. В первом случае я создаю таблицу, которую использую для работы с одной строкой и тут же её удаляю. Во втором случае приходится писать длинную-длинную команду вставки.
Извиняюсь за "много букоФФ" - коротко не смог описать проблему.
Спасибо всем, кто внимательно дочитал до этого места И особое спасибо тем, кто скажет, как тут лучше поступить. Возможно, мой "вариант 1" является достаточно оптимальным и я просто "парю себе мозХ" такой проблемой, которая не существует? Хотя, конечно, хотелось бы просто сформировать одну динамическую команду вставки и не думать особо много. Но для этого надо, чтобы дата возвращалась обрамленной в кавычки.
Прочитал 2 раза, так и ничего
Прочитал 2 раза, так и ничего не понял. Но задам такой вопрос. А зачем вам EXCEPTION?
Почему не использовать: CREATE TABLE ... IF NOT EXISTS?
Насчет CREATE TABLE ... IF
Насчет CREATE TABLE ... IF NOT EXISTS я не подумал, есть такое дело . Но это не решит проблему, хотя, возможно, и упростит код.
Основная проблема в том, что при формировании динамической команды вставка NEW туда идет криво.... То есть, если я вставляю NEW как строку в запрос с динамически формируемым именем таблицы, то тогда получается бардак.
Еще раз повторю часть кода из первого сообщения. Вот такая строка формирует запрос:
request := 'insert into ' || new_table || ' values ' || NEW;
И я получаю вот такой запрос на выходе:
INSERT INTO block_181_2012_w14 VALUES (19,"Family name and name",100,2012-04-05)
То есть, символьные строки в двойных кавычках (что можно решить), а дата - без кавычек, что и составляет основную проблему.
Попробуйте дописать
Попробуйте дописать недостающие одинарные кавычи, типа:
request := 'insert into ' || new_table || ' values ''' || NEW || ';
Там, вообще-то, ВНУТРИ строки
Там, вообще-то, ВНУТРИ строки не хватает кавычек Около поля с датой.
Запросите из NEW поля по
Запросите из NEW поля по отдельности и приделайте им кавычки как вам нужно. Вы же уже делали это в показанных вами примерах.
Как я писал ранее, это
Как я писал ранее, это решение мне не нравится.
Во-первых потому, что таблиц много с разной структурой, зачастую с десятками-сотнями полей. И мне придется для каждой из них писать вот эти длинные строки.
Во-вторых, при изменении структуры таблиц придется изменять триггеры. И самое главное - надо помнить о том, что триггеры надо изменить!
Короче говоря, как я понял, никто не может ничего подсказать полезного... Придется на "варианте 1" остановиться - том, что у меня указан первым в списке найденных мной решений. Плохо, что нет более универсального и простого решения.
А почему мне не нравится этот вариант? Потому что данные заливаются через COPY, в большом количестве. И при заливке каждой строки создавать таблицу и тут же её удалять... По моему мнению, это "не есть хорошо".
Все просто
Сам искал решение такой проблемы. Вот нашел рабочее:
EXECUTE 'INSERT INTO'|| table_name||'SELECT $1.*' USING NEW
БЛИН... Действительно просто! :)))
Весьма и весьма признателен. "Крутился" около этого варианта, но так и не сообразил, как правильно будет. И ведь просто всё получается!!!
Теперь всё просто "полетело".
Огромная благодарность автору
Огромная благодарность автору такого простого решения проблемы! Я так же как и автор задачи долго мучался над похожей задачей... А оказывается все решается так просто!