функция/процедура для обновления (ALTER) другой функции/процедуры

У меня есть секционированные таблицы в PostgreSQL 9.2, каждая из которых имеет функцию секционирования, которая выглядит следующим образом:

CREATE OR REPLACE FUNCTION myPartitionSelectionFunction()
  RETURNS trigger AS
$BODY$
BEGIN
    IF ( NEW.PartitionColumn < DATE '2010-08-08 00:00:00') THEN INSERT INTO MyPartitionedTable_Week_31_2010 VALUES (NEW.*);
    ELSIF ( NEW.PartitionColumn < DATE '2010-08-15 00:00:00' AND NEW.PartitionColumn >=  DATE '2010-08-08 00:00:00') THEN INSERT INTO MyPartitionedTable_Week_32_2010 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'PartitionColumn out of range. Update myPartitionSelectionFunction()';
    END IF;
    RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql

Итак, каждую неделю — новый раздел. Проблема заключается в обновлении сотен функций каждую неделю. Я должен автоматизировать это, и триггер для каждой вставки невозможен. Идея состоит в том, чтобы создать функцию, которую будет вызывать cron, которая обновляет каждую функцию раздела, добавляя еще один ELSIF перед окончательным ELSE. Однако я не могу найти способ выполнить итерацию по каждой функции, имеющей имя «раздел», а затем получить ее код (команды psql, такие как \d, не будут работать в функции, верно?), чтобы добавить новый ELSIF и соответственно обновите (ALTER) каждую функцию, создав в процессе новый раздел.

Итак, самый большой вопрос, который у меня есть сейчас:

Как выполнить итерацию (используя курсор?) через каждую функцию, которая имеет имя «раздела», затем получить ее код, добавить еще один ELSIF перед ELSE и обновить (ALTER) его содержимое, не рискуя застрять в бесконечном цикле?

Буду признателен за любую оказанную помощь. Спасибо.


postgresql function stored-procedures database-partitioning postgresql-9.2
person Lynx Kepler    schedule 19.07.2013    source источник
comment
Запрос: SELECT proname, prosrc FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_proc p ON pronamespace = n.oid WHERE nspname = 'public'; дает нам определение процедуры/функции.   -  person Lynx Kepler    schedule 22.07.2013

Ответы (1)


arrow_upward
4
arrow_downward

Хотя вы можете получить исходный код функции из pg_proc, попытка получить его, отредактировать и обновить функцию — это рецепт боли и страданий.

Вместо этого напишите процедуру PL/PgSQL, которая генерирует текст функции целиком. Используйте запрос к information_schema или pg_catalog.pg_class, чтобы получить требуемый список таблиц, ограничения, наследование и т. д. Прокрутите результаты запроса, создав тело функции, затем объедините все это в один блок текста и передайте его EXECUTE.

Вот игрушечный пример того, как одна функция генерирует другую:

CREATE OR REPLACE FUNCTION very_meta(func_name text, message text) RETURNS void AS 
$$
DECLARE
    func_lines text;
BEGIN
    -- In reality you'd build this iteratively, or preferably use `string_agg` over
    -- a query, but I'm just going to supply a single line function body for this
    -- example:
    func_lines := format($LINE$RAISE NOTICE 'It works, message is %%!','%s';$LINE$, message);
    -- Now, build the function creation statement and execute it:
    EXECUTE format(
    $INNER$
        -- this is the SQL text we're going to execute, with the %%I placeholder
        -- to be replaced by the format(...) function:
        --
        CREATE OR REPLACE FUNCTION %I() RETURNS void AS
        $INNERBODY$
        BEGIN
            -- and this is the body of the function we're generating
            -- in this case it's going to be substituted in as func_lines
            -- by format(...)
            %s
        END;
        $INNERBODY$
        LANGUAGE plpgsql;
    $INNER$, func_name, func_lines);
END;
$$ LANGUAGE plpgsql;

Демо:

regress=> SELECT very_meta('lessmeta', 'Secret Message');
 very_meta 
-----------

(1 row)

regress=> SELECT lessmeta();
NOTICE:  It works, message is Secret Message!
 lessmeta 
----------

(1 row)

Это можно комбинировать с соответствующими запросами к information_schema и/или системным каталогам для получения списков таблиц, создания серии условных тестов и т. д. Это ответ Я написал некоторое время назад подробности о том, как запрашивать каталоги для отношений наследования, что может быть полезно, но вам также необходимо определить используемое ограничение CHECK. Если у вас есть соглашение об именах, может быть проще просто положиться на него и выполнить поиск pg_class.

Имейте в виду, что разделение PostgreSQL лучше всего работает с несколькими сотнями таблиц, если это так. Производительность значительно снизится из-за затрат на планирование запросов с большим количеством таблиц.

person Craig Ringer    schedule 20.07.2013