Работа с partitions, перенос данных в нужную таблицу - есть вопрос при динамическом создании таблиц.

С Партишенс разобрался "с пол-пинка", всё работает отлично. До тех пор, пока я не захотел сделать создание таблиц динамически. То есть, я не хочу заранее создавать таблицы "впрок", а чтобы это делалось внутри триггера вставки данных. И вот тут наступил на грабли... Либо я что-то "не так" делаю, либо такая возможность не особо предусматривалась?

Итак. Есть "базовая" таблица, для неё делаю триггер вставки данных. Внутри триггера определяю вставку данных в другую таблицу. Особенность в том, что имя таблицы определяется так:

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

БЛИН... Действительно просто! :)))

Весьма и весьма признателен. "Крутился" около этого варианта, но так и не сообразил, как правильно будет. И ведь просто всё получается!!!

Теперь всё просто "полетело".

Огромная благодарность автору

Огромная благодарность автору такого простого решения проблемы! Я так же как и автор задачи долго мучался над похожей задачей... А оказывается все решается так просто!

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Back to top

(С) Виктор Вислобоков, 2008-2023