/**
 * Creates a view to get all functions of the current database.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE VIEW pg_functions AS
SELECT pg_namespace.nspname AS schema_name
	, pg_proc.proname AS function_name
	, pg_catalog.pg_get_function_result(pg_proc.oid) AS returning_data_type
	, pg_catalog.pg_get_function_arguments(pg_proc.oid) AS parameters
	, CASE
			WHEN pg_proc.prokind = 'a' THEN
				'aggregate'
			WHEN pg_proc.prokind = 'w' THEN
				'window'
			WHEN pg_proc.prokind = 'f' THEN
				'function'
			WHEN pg_proc.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN
				'trigger'
			ELSE
				'unknown'
		END AS function_type
	, pg_description.description AS function_comment
FROM pg_catalog.pg_proc
	LEFT OUTER JOIN pg_catalog.pg_namespace
		ON pg_proc.pronamespace = pg_namespace.oid
	LEFT OUTER JOIN pg_catalog.pg_description
		ON pg_proc.oid = pg_description.objoid
WHERE pg_catalog.pg_function_is_visible(pg_proc.oid)
	AND pg_namespace.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schema_name
	, function_name
	, parameters
;

-- Add a comment
COMMENT ON VIEW pg_functions IS 'The view returns all functions of the current database, excluding those in the schema pg_catalog and information_schema';
COMMENT ON COLUMN pg_functions.schema_name IS 'The name of the schema where the function is stored in.';
COMMENT ON COLUMN pg_functions.function_name IS 'The name of the function or procedure.';
COMMENT ON COLUMN pg_functions.returning_data_type IS 'The data type that the function is returning.';
COMMENT ON COLUMN pg_functions.parameters IS 'Parameters used to call the function.';
COMMENT ON COLUMN pg_functions.function_type IS 'The type of function, can be aggregate, window, function, trigger, or unknown.';
COMMENT ON COLUMN pg_functions.function_comment IS 'The comment, that is added to descrbe the function.';
