Menu
I want to get the SQL to drop a function in PostgreSQL. I write DROP FUNCTION and a get function name from pg_proc. That is not problem. However if I leave blank parameters it will not drop the function.
I checked the manual and there is written then I have to identify the function with its parameters to drop it, eg DROP FUNCTION some_func(text,integer) not just DROP FUNCTION some_func.
Where can I find the parameters? In the function's row on in the pg_proc table there is no parameters. So how can I get the SQL to drop the function?
931
To execute DROP FUNCTION, at a minimum, a user must have ALTER permission on the schema to which the function belongs, or CONTROL permission on the function.
args() function in R Language is used to get the required arguments by a function. It takes function name as arguments and returns the arguments that are required by that function.
There are two ways to pass arguments to a function: by reference or by value. Modifying an argument that's passed by reference is reflected globally, but modifying an argument that's passed by value is reflected only inside the function.
DROP FUNCTION removes the definition of an existing function. To execute this command the user must be the owner of the function. The argument types to the function must be specified, since several different functions can exist with the same name and different argument lists.
Postgres has a dedicated function for that purpose. Introduced with Postgres 8.4. The manual:
pg_get_function_identity_arguments(func_oid)... get argument list to identify a function (without default values) ...
pg_get_function_identity_argumentsreturns the argument list necessary to identify a function, in the form it would need to appear in withinALTER FUNCTION, for instance. This form omits default values.
Using that (and format(), introduced with Postgres 9.1), the following query generates DDL statements to drop functions matching your search terms:
SELECT format('DROP %s %I.%I(%s);' , CASE WHEN p.proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END , n.nspname , p.proname , pg_catalog.pg_get_function_identity_arguments(p.oid) ) AS stmt FROM pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE p.proname = 'dblink' -- function name -- AND n.nspname = 'public' -- schema name (optional) -- AND pg_catalog.pg_function_is_visible(p.oid) -- function visible to user ORDER BY 1; The system catalog pg_proc changed in Postgres 11. proisagg was replaced by prokind, true stored procedures were added. You need to adapt. See:
Returns:
stmt --------------------------------------------------- DROP FUNCTION public.dblink(text); DROP FUNCTION public.dblink(text, boolean); DROP FUNCTION public.dblink(text, text); DROP FUNCTION public.dblink(text, text, boolean); Found four matches in the example because dblink uses overloaded functions.
Run DROP statements selectively!
Alternatively, you can use the convenient cast to the object identifier type regprocedure which returns a complete function signature including argument types:
-- SET LOCAL search_path = ''; -- optional, to get all names schema-qualified SELECT format('DROP %s %s;' , CASE WHEN proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END , oid::regprocedure ) AS stmt FROM pg_catalog.pg_proc WHERE proname = 'dblink' -- function name ORDER BY 1; In Postgres 10, you can delete a function without knowing the list of parameters, as long as it is unique in its schema.
drop function if exists some_func;
See the docs.
Of course, if you have overloaded the function (or are trying to delete over multiple schemas), you will still need the above answers.
36
use pgadminIII and direct access to function list and right click it then select delete
33
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
