0

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?

MD NASEEM
  • 1
  • 2
  • 1
    PostgreSQL supports overloaded functions at the schema level, so the signature is part of the function identification. This is the expected and correct behavior in PostgreSQL. Oracle only considers the function name at the schema level, but supports overloading in PL/SQL packages. – JohnH Mar 23 '23 at 15:01

1 Answers1

0

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) ...
J Spratt
  • 1,762
  • 1
  • 11
  • 22
  • There are a couple of issues with the DROP then CREATE pattern. One issue is that dependent objects, such as views, have to be dropped and recreated as well. Another issue is that granted permissions are lost when objects are dropped. These concerns might be easily addressed when everything is in a single repository; however, it is much more complicated when objects external to the repository depend on objects affected by the DROP/CREATE. – JohnH Mar 24 '23 at 17:46
  • Yes, it is true that you need to cascade to the dependent objects. The granted permissions are probably the trickiest bit outside of external dependencies which is a different topic IMO. In general, I typically process DDL files in an order that can address those concerns. Something like the following: Role defs, schema/table defs, trigger defs, function defs, view defs, grant privileges. – J Spratt Mar 27 '23 at 14:17