УДАЛИТЬ ФУНКЦИЮ, не зная количество / тип параметров?
Я храню все свои функции в текстовом файле с 'CREATE OR REPLACE FUNCTION somefunction', Поэтому, если я добавляю или изменяю какую-то функцию, я просто передаю файл в psql.
Теперь, если я добавляю или удаляю параметры в существующей функции, она создает перегрузку с тем же именем и для удаления необходимого мне исходного типа все типы параметров в точном порядке, что довольно утомительно.
Есть ли какой-то подстановочный знак, который я могу использовать для удаления всех функций с заданным именем, чтобы я мог просто добавить DROP FUNCTION строки в начало моего файла?
6 ответов
Вам нужно написать функцию, которая взяла бы имя функции и проверяла каждую перегрузку с ее типами параметров из information_schemaзатем построил и выполнил DROP для каждого.
РЕДАКТИРОВАТЬ: Это оказалось намного сложнее, чем я думал. Это выглядит как information_schema не хранит необходимую информацию о параметрах в своем routines каталог. Так что вам нужно использовать дополнительные таблицы PostgreSQL pg_proc а также pg_type:
CREATE OR REPLACE FUNCTION udf_dropfunction(functionname text)
RETURNS text AS
$BODY$
DECLARE
funcrow RECORD;
numfunctions smallint := 0;
numparameters int;
i int;
paramtext text;
BEGIN
FOR funcrow IN SELECT proargtypes FROM pg_proc WHERE proname = functionname LOOP
--for some reason array_upper is off by one for the oidvector type, hence the +1
numparameters = array_upper(funcrow.proargtypes, 1) + 1;
i = 0;
paramtext = '';
LOOP
IF i < numparameters THEN
IF i > 0 THEN
paramtext = paramtext || ', ';
END IF;
paramtext = paramtext || (SELECT typname FROM pg_type WHERE oid = funcrow.proargtypes[i]);
i = i + 1;
ELSE
EXIT;
END IF;
END LOOP;
EXECUTE 'DROP FUNCTION ' || functionname || '(' || paramtext || ');';
numfunctions = numfunctions + 1;
END LOOP;
RETURN 'Dropped ' || numfunctions || ' functions';
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Я успешно проверил это на перегруженной функции. Он был собран довольно быстро, но прекрасно работает как функция полезности. Я бы рекомендовал протестировать больше, прежде чем использовать его на практике, на случай, если я что-то упустил.
Основной запрос
Этот запрос создает все необходимые операторы DDL (упрощено с приведением к regprocedure):
SELECT 'DROP FUNCTION ' || oid::regprocedure
FROM pg_proc
WHERE proname = 'my_function_name' -- name without schema-qualification
AND pg_function_is_visible(oid); -- restrict to current search_path ..
-- .. you may or may not want this
Выход:
DROP FUNCTION my_function_name(string text, form text, maxlen integer);
DROP FUNCTION my_function_name(string text, form text);
DROP FUNCTION my_function_name(string text);
Выполните команды (после проверки достоверности).
Имя функции чувствительно к регистру и без добавления двойных кавычек при передаче в виде text параметр для сравнения pg_proc.proname,
Приведение к типу идентификатора объекта regprocedure (oid::regprocedure) делает все идентификаторы безопасными от внедрения SQL (посредством злонамеренно искаженных идентификаторов). При конвертации в text имя функции заключено в двойные кавычки и дополнено схемой в соответствии с текущим search_path автоматически, где это необходимо.
pg_function_is_visible(oid) ограничивает выбор функциями в текущем search_path, Вы можете или не можете этого хотеть. С условием pg_function_is_visible(oid) на месте, функция гарантированно будет видна.
Если у вас есть несколько функций с одним и тем же именем в нескольких схемах или перегружены функции с различными аргументами функций, все они будут перечислены отдельно. Вы можете хотеть ограничиться определенной схемой (ами) или определенным параметром (ами) функции в конце концов.
Связанные с:
функция
Вы можете построить plpgsql Функция вокруг этого, чтобы выполнить операторы сразу с EXECUTE, Для Postgres 9.1 или новее: Осторожно! Это падает ваши функции!
CREATE OR REPLACE FUNCTION f_delfunc(_name text, OUT func_dropped int) AS
$func$
DECLARE
_sql text;
BEGIN
SELECT count(*)::int
, 'DROP FUNCTION ' || string_agg(oid::regprocedure::text, '; DROP FUNCTION ')
FROM pg_proc
WHERE proname = _name
AND pg_function_is_visible(oid)
INTO func_dropped, _sql; -- only returned if trailing DROPs succeed
IF func_dropped > 0 THEN -- only if function(s) found
EXECUTE _sql;
END IF;
END
$func$ LANGUAGE plpgsql;
Вызов:
SELECT * FROM f_delfunc('my_function_name');
Или просто:
SELECT f_delfunc('my_function_name');
Таким образом, вы не получите имя столбца func_dropped для столбца результата. Может не важно для вас.
Функция возвращает количество найденных и удаленных функций (исключений не возникло) - 0 если ничего не было найдено.
Предполагается (по умолчанию) search_path где pg_catalog не был перемещен.
Больше в этих связанных ответах:
- Как search_path влияет на разрешение идентификатора и "текущую схему"
- Усечение всех таблиц в базе данных Postgres
- Параметризованный в PostgreSQL Order By / Limit в табличной функции
Для версий Postgres старше 9.1 или более старых вариантов использования функции regproc а также pg_get_function_identity_arguments(oid) проверьте историю редактирования этого ответа.
Улучшение исходного ответа для того, чтобы принять schema во внимание, т.е. schema.my_function_name,
select
format('DROP FUNCTION %s(%s);',
p.oid::regproc, pg_get_function_identity_arguments(p.oid))
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE
p.oid::regproc::text = 'schema.my_function_name';
pgsql генерирует ошибку, если существует более одной процедуры с одинаковым именем, но разными аргументами, когда процедура удаляется в соответствии с ее именем. Таким образом, если вы хотите удалить одну процедуру, не затрагивая другие, просто используйте следующий запрос.
SELECT 'DROP FUNCTION ' || oid::regprocedure
FROM pg_proc
WHERE oid = {$proc_oid}
Слегка улучшенная версия ответа Эрвина. Дополнительно поддерживает следующее
- "как" вместо точного соответствия имени функции
- может работать в "сухом режиме" и "отслеживать" SQL для удаления функций
Код для копирования / вставки:
/**
* Removes all functions matching given function name mask
*
* @param p_name_mask Mask in SQL 'like' syntax
* @param p_opts Combination of comma|space separated options:
* trace - output SQL to be executed as 'NOTICE'
* dryrun - do not execute generated SQL
* @returns Generated SQL 'drop functions' string
*/
CREATE OR REPLACE FUNCTION mypg_drop_functions(IN p_name_mask text,
IN p_opts text = '')
RETURNS text LANGUAGE plpgsql AS $$
DECLARE
v_trace boolean;
v_dryrun boolean;
v_opts text[];
v_sql text;
BEGIN
if p_opts is null then
v_trace = false;
v_dryrun = false;
else
v_opts = regexp_split_to_array(p_opts, E'(\\s*,\\s*)|(\\s+)');
v_trace = ('trace' = any(v_opts));
v_dryrun = ('dry' = any(v_opts)) or ('dryrun' = any(v_opts));
end if;
select string_agg(format('DROP FUNCTION %s(%s);',
oid::regproc, pg_get_function_identity_arguments(oid)), E'\n')
from pg_proc
where proname like p_name_mask
into v_sql;
if v_sql is not null then
if v_trace then
raise notice E'\n%', v_sql;
end if;
if not v_dryrun then
execute v_sql;
end if;
end if;
return v_sql;
END $$;
select mypg_drop_functions('fn_dosomething_%', 'trace dryrun');
Вот запрос, который я построил поверх решения @Сухой27, которое генерирует операторы sql для удаления всех хранимых функций в схеме:
WITH f AS (SELECT specific_schema || '.' || ROUTINE_NAME AS func_name
FROM information_schema.routines
WHERE routine_type='FUNCTION' AND specific_schema='a3i')
SELECT
format('DROP FUNCTION %s(%s);',
p.oid::regproc, pg_get_function_identity_arguments(p.oid))
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE
p.oid::regproc::text IN (SELECT func_name FROM f);
Начиная с Postgres 10, вы можете удалять функции только по имени, если имена уникальны для их схемы. Просто поместите следующее объявление вверху вашего файла функции:
drop function if exists my_func;
Документация здесь.