Exception raised in get_routine_ddl when several functions have the same name (but different set of parameters)

[loicmalassis]

First of all, thank you guys for having developed this extension.

There is one little bug, when several functions are defined with the same name but different sets of parameters, for example:

1. function my_schema.my_function(char, char)
2. function my_schema.my_function(char, char, int)

The function get_routine_ddl is expecting one single oid for a given schema+function
Hence it creates an exception

Here is the proposed updated code, which provides the concatenated DDL (for each different "instance" of the function)

I hope it helps

Take care

---

CREATE OR REPLACE FUNCTION dbms_metadata.get_routine_ddl(schema_name text, routine_name text, routine_type text DEFAULT 'procedure'::text)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE
l_oid oid;
routine_code text;
routine_type_flag text;
l_sqlterminator_guc boolean;
BEGIN
-- Initialize transform params if they have not been set before
PERFORM dbms_metadata.init_transform_params();

-- Getting values of transform params
SELECT current_setting('DBMS_METADATA.SQLTERMINATOR')::boolean INTO l_sqlterminator_guc;

CASE WHEN routine_type = 'PROCEDURE' THEN
    routine_type_flag = 'p';
WHEN routine_type = 'FUNCTION' THEN
    routine_type_flag = 'f';
END CASE;

-- Commented by LMA: original code, which was handling one single oid
-- SELECT dbms_metadata.get_object_oid(routine_type, schema_name, routine_name) INTO l_oid;
-- 
-- SELECT
--     pg_get_functiondef(p.oid) INTO STRICT routine_code
-- FROM
--     pg_proc p
-- WHERE
--     p.oid = l_oid
--     AND p.prokind = routine_type_flag; 

-- Added by LMA, to handle multiple oid
select string_agg( pg_get_functiondef(p.oid), ';'||chr(13) ) INTO STRICT routine_code
FROM
    pg_proc p
WHERE
    p.oid in (SELECT oid FROM pg_proc WHERE proname = routine_name AND pronamespace = dbms_metadata.get_schema_oid(schema_name) )
    AND p.prokind = routine_type_flag;     
   
-- Commented by LMA, to avoid having multiple ";"
-- IF l_sqlterminator_guc THEN
--     routine_code := concat(routine_code, ';');
-- END IF;
RETURN routine_code;

EXCEPTION
WHEN NO_DATA_FOUND THEN
IF schema_name IS NULL THEN
RAISE EXCEPTION '% with name % not found. Please provide schema name.', routine_type, routine_name;
ELSE
RAISE EXCEPTION '% with name % not found in schema %', routine_type, routine_name, schema_name;
END IF;
END;
$function$
;

[Akil1470]

Hello @loicmalassis Thanks for the code.

We have support for the overloaded functions in the roadmap. I don't know if we need to return DDL concatenation of all the overloaded functions. Because oracle's dbms_metadata.get_ddl is returning DDL of a single overloaded function when we try to get DDL of overloaded function(randomly it is picking one of overloaded functions I guess). As this extension is counterpart for oracle dbms_metadata, we need to provide similar results. We will try to add this support soon.