postgresql: drop function with return value without knowing it.

Unlike Oracle, Postgresql do not distinct between procedure but considering procedure as function with out return value. Technically, they are the same just procedure without return value  but would be nice to differentiate them. The inconvenient comes when Procedure and Function are considered as the same object type in Postgresql which is function. Dropping a function without parameter would be the way way but what about dropping function with parameter? It is not as easy as Oracle in this matter. Came up with this script and I was able to drop it.

-- Check if the function in the database

vcdb=# \df remove_cafe_resource;

                                      List of functions

 Schema |         Name         | Result data type |       Argument data types       |  Type

--------+----------------------+------------------+---------------------------------+--------

 public | remove_cafe_resource | void             | resource_name character varying | normal

(1 row)

-- Attempt to drop the function without parameter it should fail but telling me that it does not exist instead.

vcdb=# drop function remove_cafe_resource();

ERROR:  function remove_cafe_resource() does not exist

-- Script to drop it. Copy and paste it to drop the function.

select 'drop function ' || oid::regproc  || '(' || pg_get_function_identity_arguments(oid) || ');' FROM   pg_proc WHERE  proname = 'remove_cafe_resource' AND pg_function_is_visible(oid);

                               ?column?

----------------------------------------------------------------------

 drop function remove_cafe_resource(resource_name character varying);

(1 row)

-- Dropped

drop function remove_cafe_resource(resource_name character varying);

DROP FUNCTION

vcdb=#  \df remove_cafe_resource;

                       List of functions

 Schema | Name | Result data type | Argument data types | Type

--------+------+------------------+---------------------+------

(0 rows)