--============================= PROCEDURES --===========================================

select
	distinct
	create_script ,
	params,
	param_mode,
	script_body ,
	param_position
from
	(
	select
		'CREATE OR REPLACE PROCEDURE ' 
|| r.routine_schema || '.' || r.routine_name || ' (' as create_script,
    
		p.parameter_name || ' ' || p.data_type as Params
		,
		p.ordinal_position as param_position,
		(case
			when p.parameter_mode = 'OUT' then p.parameter_mode
			else ''
		end) as param_mode,
		p.parameter_mode ,
		') AS ' || r.routine_definition as script_body
	from
		information_schema.routines r
	left join 
    information_schema.parameters p 
    on
		r.specific_schema = p.specific_schema
		and r.specific_name = p.specific_name
		--GROUP BY      r.routine_schema, r.routine_name;    
	where
		r.routine_type = 'PROCEDURE'
		-- Filter for procedures
		and r.routine_schema not in ('information_schema', 'pg_catalog')
		-- Exclude system schemas
		and r.routine_name like 'test_sp1%'
	)
order by
	create_script,
	param_position;
	
	
--============================= FUNCTIONS --===========================================

select distinct
	create_script,
	params,param_position
	,parameter_mode
from
	(
	select
		'CREATE OR REPLACE FUNCTION ' 
|| r.routine_schema || '.' || r.routine_name || ' (' ||
    ') ' || r.routine_definition as create_script,
		p.parameter_name || ' ' || p.data_type as Params
		,p.ordinal_position as param_position
		,p.parameter_mode 
	from
		information_schema.routines r
	left join 
    information_schema.parameters p 
    on
		r.specific_schema = p.specific_schema
		and r.specific_name = p.specific_name
		--GROUP BY      r.routine_schema, r.routine_name;    
	where
		r.routine_type = 'FUNCTION'
		-- Filter for procedures
		and r.routine_schema not in ('information_schema', 'pg_catalog')
		-- Exclude system schemas
	) ;