Attach comments to functions' parameters and return value - Mailing list pgsql-hackers

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,
'Returnsa 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
titlelevel 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
arraywith 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
descriptionof 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
theposition 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
thatcreates 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