Hello In PostgreSQL it is possible to attach comments to almost everything. This made it possible for us to integrate the wiki that we use for our technical documentation directly with the database using the MediaWiki [1] extensions ExternalData [2] and MagicNoCache [3]. The result is a documentation on tables and related objects (indexes, triggers, etc.) and views that always shows the current state, i.e. any DDL change or any comment attached to an object is shown in the wiki immediately (or on refresh if the change was done after the reader landed on the page).
In order to optimize the query, we wrote a small set of sql functions that generate wiki markup for the objects queried. The idea behind is that this is much quicker in PostgreSQL than on a web server hosting MediaWiki, besides a better control of the privileges for the user retrieving data. So far we can document in such a way tables and views. I started to create something similar for functions until I noticed that there is no way to attach comments on functions' parameters and return value. My first idea was to add this information in the function description, but this is quite an ugly solution. My next workaround is to simulate the behaviour of a COMMENT ON FUNCTION PARAMETER/RETURNVALUE command inserting comments on these directly in pg_description. For that I used a convention similar to the one used for table attributes and defined following pg_description.objsubid: -1 = return value 0 = comment on the function itself (this already exists) 1..n = comment on parameter at position n An insert would then look like: INSERT INTO pg_catalog.pg_description VALUES ('function_name(param_type_list)'::regprocedure, 'pg_proc'::regclass, parameter_position, 'Comment'); With a simple function similar to the one used to query column descriptions (pg_catalog.col_description), it is possible to get the comments. CREATE OR REPLACE FUNCTION pg_catalog.param_description (objoid OID, posnum INTEGER) RETURNS TEXT STABLE AS $$ SELECT description FROM pg_catalog.pg_description WHERE objoid = $1 AND classoid = 'pg_catalog.pg_proc'::pg_catalog.regclass AND objsubid = $2; $$ LANGUAGE SQL; Example: INSERT INTO pg_catalog.pg_description VALUES ('public.create_wiki_doc(integer,text[],text[])'::regprocedure, 'pg_proc'::regclass, -1, 'Returns a set of TEXT with wiki formatted description of tables and views'); INSERT INTO pg_catalog.pg_description VALUES ('public.create_wiki_doc(integer,text[],text[])'::regprocedure, 'pg_proc'::regclass, 1, 'Wiki title level for each table documentation. The number of "=" to put before and after the name of the object'); VALUES ('public.create_wiki_doc(integer,text[],text[])'::regprocedure, 'pg_proc'::regclass, 2, 'An array with the list of schemas to be documented'); Etc. SELECT param_description('public.create_wiki_doc(integer,text[],text[])'::regproced ure,-1); param_description --------------------------------------------------------------------------- Returns a set of TEXT with wiki formatted description of tables and views SELECT param_description('public.create_wiki_doc(integer,text[],text[])'::regproced ure,1); param_description ---------------------------------------------------------------------------- ------------------------------------- Wiki title level for each table documentation. The number of "=" to put before and after the name of the object SELECT param_description('public.create_wiki_doc(integer,text[],text[])'::regproced ure,2); param_description ---------------------------------------------------- An array with the list of schemas to be documented Etc. As I said this is just a workaround and it is not comfortable to manipulate catalog tables directly. The much better solution would be to have an implementation of the sql comment command for parameters and return value of functions built in the system. So my questions on that topic to the community: - Is there a reason why this approach should not be followed (currently as workaround later as implementation in C)? - Is somebody already doing implementation work in this area or would be interested in engaging? - Is there a general interest for that addition? - Any good advice, tips, suggestions? I was not completely inactive. I started looking into the code and I am, honestly, a bit puzzled (see below). If I were to take up the job, which I would love, I guess that this could not be before the beginning of November this year. With some help, however, I may be able to start earlier. What I could find so far looking at the documentation and the code is that there are quite a number of files to be touched. The given snippets are just for illustration: - src/include/nodes/parsenodes.h : Define object types e.g. FUNCTION_PARAMETER, FUNCTION_RETURNVALUE - src/backend/catalog/objectaddress.c : - Add mappings for new types in struct object_type_map - Add function to get the position of the parameter, e.g. LookupFuncNamePos(function_name, param_name) or a function to create the whole ObjectAddress e.g. . - Add code for address in ObjectAddress get_object_address(ObjectType objtype, List *objname, List *objargs, Relation *relp, LOCKMODE lockmode, bool missing_ok) [...] case FUNCTION_PARAMETER: address.classId = ProcedureRelationId; address.objectId = LookupFuncNameTypeNames(objname, objargs, missing_ok); address.objectSubId = LookupFuncNamePos(function_name, param_name); break; case FUNCTION_RETURNVALUE: address.classId = ProcedureRelationId; address.objectId = LookupFuncNameTypeNames(objname, objargs, missing_ok); address.objectSubId = -1; break; [...] Or, if there is a function that creates the whole ObjectAddress, simply return that. - Add code for ownership of function in void check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address, List *objname, List *objargs, Relation relation) - src/backend/parser/gram.y : Add bison grammar entries to initialize a CommentStmt struct for new elements. - src/backend/commands/command.c : Add code to check that these are values of a function (analogue to column for table and co.) in ObjectAddress CommentObject(CommentStmt *stmt) To long time PostgreSQL developers this may look straightforward. For the moment I am not even sure if that is correct and if there are other places that would need additions, apart from the obvious display in psql. Sorry for the long post and thank you if you find time to address one or more of my questions. Bye Charles [1] https://www.mediawiki.org/wiki/MediaWiki/ [2] https://www.mediawiki.org/wiki/Extension:External_Data [3] https://www.mediawiki.org/wiki/Extension:MagicNoCache -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers