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

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$ ;

HexaCluster / pg_dbms_metadata

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