How to make behaviour of create or replace function function_name (param_name data_type) same in oracle and edb?

Question

In oracle database following syntax create or replaces function function_name even if we only change the function signature.(i.e data type of function. like varchar to varchar2) create or replace function function_name(numstr Varchar) // function body /

but in case of edb/open source postgres above query and changes will create new multiple function with same name but different datatypes.

 SELECT oid::regprocedure FROM pg_proc WHERE proname = 'function_name';
            

oid

function_name(integer) function_name(character) function_name(character varying) (3 rows)

What i am missing here, because replace should update existing function and it should not create new function?

Answer 1

You can use CREATE OR REPLACE schema_name.function_name(param_a VARCHAR, param_b TEXT). However, you're correct that Postgres will allow for overloaded functions. So, if you want to get rid of a function that has the same name but a different signature than the one you're trying to CREATE OR REPLACE, you need to use DROP FUNCTION IF EXISTS schema_name.function_name(param_a VARCHAR);.

The tactic I take in my repo is to call DROP on the old function signature right before the new function definition.

DROP FUNCTION IF EXISTS schema_name.function_name(VARCHAR);
CREATE OR REPLACE FUNCTION schema_name.function_name(param_a VARCHAR, param_b INTEGER)
RETURNS some_type
AS $$
DECLARE
BEGIN
    -- body
END;
$$

---

There is no inherent problem with having different parameter types in a function signature with the same arity. Depending on what type is passed from the client, Postgres will choose the right function. So having two signatures like the following is valid:

CREATE OR REPLACE schema_name.function_name(param_a TEXT) ...
CREATE OR REPLACE schema_name.function_name(param_a INTEGER) ...