Thread: Attach comments to functions' parameters and return value
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
On 9/14/15 8:59 AM, Charles Clavadetscher wrote: > 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). Neat! I hope you'll open source that. :) > 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. And that! :) > 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 At first glance, seems reasonable. > - 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. . Something similar might exist already. TBH, to start with, I would only support position number. You'll have to support that case anyway, and it should be simpler. > 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. I suspect that changes to support this should be pretty localized. I suggest you look at other recent patches that have added COMMENT functionality to see what they did. BTW, I'm also interested in this but I'm not sure when I'd have time to work on it. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com
Jim Nasby wrote: > On 9/14/15 8:59 AM, Charles Clavadetscher wrote: > >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. > > I suspect that changes to support this should be pretty localized. I suggest > you look at other recent patches that have added COMMENT functionality to > see what they did. This sequence of commits may be helpful. http://git.postgresql.org/gitweb/?p=postgresql.git;a=history;f=src/test/regress/sql/object_address.sql;hb=HEAD -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Hello Jim On 14/09/2015 19:23, Jim Nasby wrote: > On 9/14/15 8:59 AM, Charles Clavadetscher wrote: >> 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). > > Neat! I hope you'll open source that. :) Thank you for your answer. Sure I will put some information on it and the source on my website soon and let you know. There is not really much magic to it, but it can be quite helpful. >> 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 > > At first glance, seems reasonable. > >> - 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. . > > Something similar might exist already. TBH, to start with, I would only > support position number. You'll have to support that case anyway, and it > should be simpler. I agree. Since parameter names are optional, supporting the position for comments definition is mandatory and is probably simpler. >> 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. > > I suspect that changes to support this should be pretty localized. I > suggest you look at other recent patches that have added COMMENT > functionality to see what they did. Good idea. As a matter of fact, Àlvaro sent a sequence of commits that relate in part to it. I will have a look to it as soon as I can. > BTW, I'm also interested in this but I'm not sure when I'd have time to > work on it. Good to know, thank you. As I mention in my original post I should be able to work on that in November this year. If I find some time I may start things earlier. Let's keep in touch over this list. Before any coding is done there should be an agreement on the syntax to be used in the statement and the way the information is stored. About the latter I think with the -1,0,1..n solution we are on a good track. For what's concerning the syntax I suggest the following. COMMENT ON FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) PARAMETER param_position IS 'text'; COMMENT ON FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) RETURN VALUE IS 'text'; An alternative to "RETURN VALUE" could be "RETURNS", which would make the statement shorter, but I think this may be confusing. The parameter list of the function is only required to identify the function also in cases it exists with the same name in different flavours. This sticks to the general syntax of the command and should be easy to understand. Your ideas? Thanks Charles
Hello Àlvaro On 14/09/2015 20:02, Alvaro Herrera wrote: > Jim Nasby wrote: >> On 9/14/15 8:59 AM, Charles Clavadetscher wrote: > >>> 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. >> >> I suspect that changes to support this should be pretty localized. I suggest >> you look at other recent patches that have added COMMENT functionality to >> see what they did. > > This sequence of commits may be helpful. > http://git.postgresql.org/gitweb/?p=postgresql.git;a=history;f=src/test/regress/sql/object_address.sql;hb=HEAD Thank you. From the first impression I think that I was not too far away from a correct procedure ;-) I will take a closer look as soon as I can, but I am confident that this will help. Bye Charles
Hello Jim > -----Original Message----- > From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers- > owner@postgresql.org] On Behalf Of Charles Clavadetscher > Sent: Dienstag, 15. September 2015 07:35 > To: Jim Nasby <Jim.Nasby@BlueTreble.com>; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Attach comments to functions' parameters and return > value > > Hello Jim > > On 14/09/2015 19:23, Jim Nasby wrote: > > On 9/14/15 8:59 AM, Charles Clavadetscher wrote: > >> 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). > > > > Neat! I hope you'll open source that. :) > > Thank you for your answer. Sure I will put some information on it and > the source on my website soon and let you know. There is not really much > magic to it, but it can be quite helpful. As promised: http://www.schmiedewerkstatt.ch/wiki/index.php/Database_self-documenting_Wik i Feedback, as usual, is always welcome. Bye Charles
On 9/15/15 12:35 AM, Charles Clavadetscher wrote: > COMMENT ON FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, > ...] ] ) PARAMETER param_position IS 'text'; > > COMMENT ON FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, > ...] ] ) RETURN VALUE IS 'text'; > > An alternative to "RETURN VALUE" could be "RETURNS", which would make > the statement shorter, but I think this may be confusing. I like RETURN VALUE better myself. > The parameter list of the function is only required to identify the > function also in cases it exists with the same name in different > flavours. This sticks to the general syntax of the command and should be > easy to understand. Right. You should be able to use the current COMMENT ON code. Note however that the last time I looked it does NOT support the full syntax that CREATE FUNCTION does. It'd be nice to fix that, but that's mostly a separate matter. Though, it would probably be nice if all of this stuff (along with the regprocedure input function) could be factored into a single piece of code... -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com