Document user-defined functions and stored procedures

[tjdavis3]

Add UDF and stored procedure documentation to the database. Some databases (eg. mysql) all a comment to create procedure that can be used. Others may need to look for comments in the DDL. At a minimum just creating a list of them (and/or a separate page that could be edited) would be helpful.

[YauhenPylAurea]

It seems this PR covers mysql, mssql and postgreSQL. Is there any other supported dbs with such functionality?

[tjdavis3]

• MariaDB is a fork of MySQL, so it should be the same
• Amazon Redshift is based on PostgreSQL, so it should be the same
• Snowflake has UDFs and StoredProcedures. Not sure of the query syntax / tables

[YauhenPylAurea]

I see that

• mariaDB is based on mysql so it should be covered within mentioned PR.
• Same for redshift
• snowflake is not covered :-( not sure how to test it properly

[tjdavis3]

The stored procedures and functions do appear now, but the presentation only works for very simple procedures. Here's what one of my current databases looks like.

Stored procedures and functions

Name	ReturnType	Arguments	Type
public.pg_stat_statements	record	showtext boolean, OUT userid oid, OUT dbid oid, OUT queryid bigint, OUT query text, OUT calls bigint, OUT total_time double precision, OUT min_time double precision, OUT max_time double precision, OUT mean_time double precision, OUT stddev_time double precision, OUT rows bigint, OUT shared_blks_hit bigint, OUT shared_blks_read bigint, OUT shared_blks_dirtied bigint, OUT shared_blks_written bigint, OUT local_blks_hit bigint, OUT local_blks_read bigint, OUT local_blks_dirtied bigint, OUT local_blks_written bigint, OUT temp_blks_read bigint, OUT temp_blks_written bigint, OUT blk_read_time double precision, OUT blk_write_time double precision	FUNCTION
public.pg_stat_statements_reset	void		FUNCTION
public.pg_buffercache_pages	record		FUNCTION
public.base36_encode	varchar	digits bigint, min_width integer DEFAULT 0	FUNCTION
public.friendly_id	varchar	digits bigint, prefix character varying DEFAULT 'M5'::character varying	FUNCTION

Some enhancements that would be nice:

• It would be nice to be able to ignore certain functions like can be done with tables. In this case I'd want to ignore pg_*.
• On the index page just list the function name and comment and link to a separate page like the tables
• The function page would then be a template like tables. It would be nice to format it much like an API document. eg.

  FUNCTION public.friendly_id(bigint, character) returns varchar

  Description (from the comment in the db)

  Parameters:

  • digits (bigint)

  • prefix (character varying DEFAULT 'M5'::character varying)

    -> Create Statement

[felixfbecker]

I really wish the functions table would show the comment of the function like for tables, columns, etc. Right now the comments are not shown anywhere which makes the table not that useful for documentation.

COMMENT ON FUNCTION foo_bar IS '...';