Funkcja DROP bez znajomości liczby / rodzaju parametrów?
Wszystkie moje funkcje przechowuję w pliku tekstowym z 'CREATE OR REPLACE FUNCTION somefunction'
.
Więc jeśli dodam lub zmienię jakąś funkcję, po prostu przesyłam plik do psql.
Teraz, jeśli dodam lub usunę parametry do istniejącej funkcji, spowoduje to przeciążenie o tej samej nazwie i aby usunąć oryginał, muszę wpisać wszystkie typy parametrów w dokładnej kolejności, co jest trochę uciążliwe.
Czy Jest jakiś rodzaj wildcard, którego mogę użyć, aby zrzucić wszystkie funkcje o danej nazwie, żebym mógł po prostu dodać DROP FUNCTION
linie na górę mojego akta?
5 answers
Trzeba by napisać funkcję, która przyjęła nazwę funkcji, i sprawdzić każde przeciążenie z jej typami parametrów z information_schema
, a następnie zbudować i wykonać DROP
dla każdego z nich.
EDIT: to okazało się o wiele trudniejsze niż myślałem. Wygląda na to, że information_schema
nie przechowuje niezbędnych informacji o parametrach w swoim katalogu routines
. Więc musisz użyć dodatkowych tabel PostgreSQL pg_proc
i 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;
Pomyślnie przetestowałem to na przeciążonej funkcji. Informatyka został wyrzucony dość szybko, ale działa dobrze jako funkcja użytkowa. Polecam przetestować więcej przed użyciem go w praktyce, na wypadek, gdybym coś przeoczył.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-10-01 21:58:52
Podstawowe zapytanie
To zapytanie tworzy wszystkie niezbędne instrukcje DDL (uproszczone z cast to 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
Wyjście:
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);
Wykonaj polecenia (po sprawdzeniu wiarygodności).
Nazwa funkcji jest uwzględniająca wielkość liter i bez dodanych podwójnych cudzysłowów, gdy jest przekazywana jako parametr text
do dopasowania do pg_proc.proname
.
Rzut na typ identyfikatora obiekturegprocedure
(oid::regprocedure
) sprawia, że wszystkie identyfikatory są bezpieczne przed SQL iniekcja (poprzez złośliwie zniekształcone identyfikatory). Podczas konwersji na text
, Nazwa funkcji jest dwukrotnie cytowana i kwalifikowana według schematu search_path
automatycznie w razie potrzeby.
pg_function_is_visible(oid)
ogranicza wybór do funkcji w bieżącym search_path
. Możesz tego chcieć lub nie. Z warunkiem pg_function_is_visible(oid)
w miejscu, funkcja jest gwarantowana być widoczne.
Jeśli masz wiele funkcji o tej samej nazwie w wiele schematów lub przeciążone funkcje z różnymi argumentami funkcji, wszystkie spośród nich zostaną wymienione oddzielnie. Możesz jednak ograniczyć się do określonych schematów lub określonych parametrów funkcji.
Powiązane:
Funkcja
Możesz zbudować wokół tego funkcję plpgsql
, aby natychmiast wykonać polecenia z EXECUTE
. Dla Postgres 9.1 lub później:
Ostrożnie! To obniża twoje funkcje!
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;
Wywołanie:
SELECT * FROM f_delfunc('my_function_name');
Lub po prostu:
SELECT f_delfunc('my_function_name');
W ten sposób nie dostaniesz kolumny nazwa func_dropped
dla kolumny result. Może nie mieć dla ciebie znaczenia.
Funkcja zwraca liczbę znalezionych i upuszczonych funkcji (bez wyjątku podniesionego) - 0
jeśli żadna nie została znaleziona.
Zakłada (domyślnie) search_path
gdzie pg_catalog
Nie został przeniesiony w pobliżu.
Więcej w tych powiązanych odpowiedziach:
- W Jaki Sposób search_path wpływa na rozdzielczość identyfikatora i "bieżący schemat"
- Obcinanie wszystkich tabel w bazie danych Postgres
- PostgreSQL parametryzował kolejność według / limitu w funkcji table
Dla wersji Postgres starszych niż 9.1 lub starszych wariantów funkcji za pomocą regproc
i pg_get_function_identity_arguments(oid)
Sprawdź historię edycji tej odpowiedzi.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-09-24 15:51:48
Poprawa oryginalnej odpowiedzi w celu uwzględnienia schema
, tj. 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';
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-07-06 06:29:20
Nieco ulepszona wersja odpowiedzi Erwina. Dodatkowo obsługuje następujące
- 'like' zamiast dokładnej nazwy funkcji dopasuj
- może działać w "trybie suchym" i "śledzić" SQL do usuwania funkcji
Kod do kopiowania / wklejania:
/**
* 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');
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-04-03 09:50:55
Oto zapytanie, które zbudowałem na rozwiązaniu @Сухой27, które generuje instrukcje sql do upuszczania wszystkich przechowywanych funkcji w schemacie:
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);
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-01-07 20:18:47