Обсуждение: comments in argument list of plpgsql get stripped?

Hi,

How can I store inline comments in the argument list of a plpgsql function to document complex overloaded functions
withlots of arguments? It seems that PostgreSQL accepts the comments, but strips them as the function gets stored. 

I am using PostgreSQL 9.1.2.

+++
CREATE FUNCTION func_test(
    -- comment for argument 1
    -- quite longish, explaining the details
    argument1 TEXT
    -- comment for argument 2
    -- even more information
    , argument2 TEXT
)
RETURNS smallint AS
$BODY$
BEGIN
-- this comment gets stored correctly
RETURN 1;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE
COST 100;
+++

According to the server log file PostgreSQL gets sent all the comments correctly, so it is not the client that already
stripsthe comments. 

+++
[...]
statement: CREATE FUNCTION func_test(
        -- comment for argument 1
        -- quite longish, explaining the details
        argument1 TEXT
        -- comment for argument 2
        -- even more information
        , argument2 TEXT
    )
    RETURNS smallint AS
    $BODY$
    BEGIN
    -- this comment gets stored correctly
    RETURN 1;
    END;
    $BODY$
    LANGUAGE plpgsql IMMUTABLE
    COST 100;
+++


select func_test('foo', 'bar');
 func_test
-----------
         1
(1 row)


Best regards,
   Ralph

On 20/01/2012 17:28, Ralph Graulich wrote:
> Hi,
>
> How can I store inline comments in the argument list of a plpgsql
> function to document complex overloaded functions with lots of
> arguments? It seems that PostgreSQL accepts the comments, but strips
> them as the function gets stored.

You could use COMMENT ON instead:

  COMMENT ON function func_test(text, text)
  IS '....loads of documentation here....';

Would this do the job?

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Hello Ray,

> You could use COMMENT ON instead:
>
>  COMMENT ON function func_test(text, text)
>  IS '....loads of documentation here....';
>
> Would this do the job?

Thank you for your input.

I know about the COMMENT ON function, as I use it for all other purposes, like documenting columns and other database
objects.- 

Within the function I can also put unlimited amount of documentation in the body of the plpgsql function to document
theinnards. This kind of comments get stored without problems. 

But in order to have the documentation where it belongs to, it would be better to put the documentation for the
argumentlist within the argument list, each comment to its argument. Let's assume you have 30 rather complex arguments
ormore you'll probably see my point - which might not be obvious in my simplified example. 

Best regards,
   Ralph

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ralph Graulich
Sent: Friday, January 20, 2012 12:28 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] comments in argument list of plpgsql get stripped?

Hi,

How can I store inline comments in the argument list of a plpgsql function
to document complex overloaded functions with lots of arguments? It seems
that PostgreSQL accepts the comments, but strips them as the function gets
stored.

I am using PostgreSQL 9.1.2.

+++
CREATE FUNCTION func_test(
    -- comment for argument 1
    -- quite longish, explaining the details
    argument1 TEXT
    -- comment for argument 2
    -- even more information
    , argument2 TEXT
)
RETURNS smallint AS
$BODY$
BEGIN
-- this comment gets stored correctly
RETURN 1;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE
COST 100;
+++

According to the server log file PostgreSQL gets sent all the comments
correctly, so it is not the client that already strips the comments.

+++
[...]
statement: CREATE FUNCTION func_test(
        -- comment for argument 1
        -- quite longish, explaining the details
        argument1 TEXT
        -- comment for argument 2
        -- even more information
        , argument2 TEXT
    )
    RETURNS smallint AS
    $BODY$
    BEGIN
    -- this comment gets stored correctly
    RETURN 1;
    END;
    $BODY$
    LANGUAGE plpgsql IMMUTABLE
    COST 100;
+++


select func_test('foo', 'bar');
 func_test
-----------
         1
(1 row)


Best regards,
   Ralph

------------------------------------------------------------------

Ralph,

The fact that you can write comments in the middle of the arguments in an
artifact of the parser and likely there is not reasonable way to get them to
persist.  You either want to use "COMMENT ON" like Raymond said or you can
simply move the comments into the body of the function.  The relevant table
that stores the function stores everything except the body as individual
fields so that it can facilitate dependency tracking and type verification,
etc...  In order to do this the input needs to be stripped of all comments
and newlines/control-characters so that only syntactically meaningful
content remains.

The fundamental issue is that the input arguments to a function are stored
as an array on pg_proc and thus to do not have their own OID with which to
link onto pg_description.

The fundamental question is by what means do you expect to be able to view
and/or modify these comments?

Feel free to provide thoughts and suggestions regarding how core could be
modified to fit your intended use-case but I would offer that unless you are
willing to fund and/or do the work that it isn't going to get much attention
due apparent need to modify the catalogs and introduce a total new way of
dealing with comments.  It is not the current policy of PostgreSQL to
capture and store original DDL but instead it parsers the DDL into the
needed catalog entries and then recombines the entries into a "normalized"
form when necessary (e.g., for pg_dump).

David J.

Hi David,

> The fact that you can write comments in the middle of the arguments in an
> artifact of the parser and likely there is not reasonable way to get them to
> persist.

"Artifact" as in "something not normally used"?

> You either want to use "COMMENT ON" like Raymond said or you can
> simply move the comments into the body of the function.

I already use the COMMENT ON to give each function a general description, an overview of what it does, which - imho -
fitsthe scope of the COMMENT ON being a comment on the whole function, rather than a particular part of, like the body,
partof the body or the argument list. 

As I showed in my example the comments within the function body gets stored and retrieved like one usually expects. Up
untilinvestigating whether I can put comments on the arguments directly in the argument list, I stored all this
informationat the head of the body, where the audit trail of the function body lives, too. However I thought about
puttingall the stuff where it logically belongs, to have a sound design. 

> The relevant table
> that stores the function stores everything except the body as individual
> fields so that it can facilitate dependency tracking and type verification,
> etc...  In order to do this the input needs to be stripped of all comments
> and newlines/control-characters so that only syntactically meaningful
> content remains.

I see your point and now understand the reason why PostgreSQL behaves the way it does, that is: stripping the comments
fromthe argument list, storing the comments within the function body. 

> The fundamental issue is that the input arguments to a function are stored
> as an array on pg_proc and thus to do not have their own OID with which to
> link onto pg_description.
>
> The fundamental question is by what means do you expect to be able to view
> and/or modify these comments?

As PostgreSQL silently accepts these comments in the argument list, I expected them to be retrievable later on, not
caringhow and where they are stored, but believing that they are stored. 
I fully understand that for the ease of parsing and having a "usable" argument list, PostgreSQL stores the argument
listinternally differently than the argument list is presented to the user and I don't dare to argue about it, because
Iaccept the fact and assume it's not only to due performance, but also to be able to use this meta information in the
databasedictionary to write queries about it (something I appreciate!). 

> Feel free to provide thoughts and suggestions regarding how core could be
> modified to fit your intended use-case but I would offer that unless you are
> willing to fund and/or do the work that it isn't going to get much attention
> due apparent need to modify the catalogs and introduce a total new way of
> dealing with comments.  It is not the current policy of PostgreSQL to
> capture and store original DDL but instead it parsers the DDL into the
> needed catalog entries and then recombines the entries into a "normalized"
> form when necessary (e.g., for pg_dump).

Well, first of all it was important to me to know that in fact I did the right thing and PostgreSQL behaves as
expected,not making an obvious mistake on the syntax of comments. 
Futhermore, now that I know its the expected way, I know how to circumvent it, looking for other means of documenting
theargument list in a sound way, like including the comments at the head of the function's body, like I already did. 

Second, not knowing about the internals of the comment system of PostgreSQL and how argument lists are handled: -

How about having something like:

I) COMMENT ON <function> (arglist-qualifier).<argumentname> IS '<comment>';  (or something similar)

and

II) Having PostgreSQL issuing a NOTICE that "comments get stripped" if you use comments somewhere where PostgreSQL
acceptsthem, but discards them silently? 

Best regards,
  Ralph

[[[ My response embedded ]]]

-----Original Message-----
From: Ralph Graulich [mailto:maillist@shauny.de]
Sent: Friday, January 20, 2012 3:24 PM
To: David Johnston
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] comments in argument list of plpgsql get stripped?

Hi David,

> The fact that you can write comments in the middle of the arguments in
> an artifact of the parser and likely there is not reasonable way to
> get them to persist.

"Artifact" as in "something not normally used"?

[[[ Maybe "by-product" is a better term; basically the parser HAS TO remove
the "--" comments and embedded newlines before it can properly parse the
names/types of the input arguments ]]]

> You either want to use "COMMENT ON" like Raymond said or you can
> simply move the comments into the body of the function.

I already use the COMMENT ON to give each function a general description, an
overview of what it does, which - imho - fits the scope of the COMMENT ON
being a comment on the whole function, rather than a particular part of,
like the body, part of the body or the argument list.

As I showed in my example the comments within the function body gets stored
and retrieved like one usually expects. Up until investigating whether I can
put comments on the arguments directly in the argument list, I stored all
this information at the head of the body, where the audit trail of the
function body lives, too. However I thought about putting all the stuff
where it logically belongs, to have a sound design.

[[[  Agreed, your desire represents a technically superior situation ]]]

> The relevant table
> that stores the function stores everything except the body as
> individual fields so that it can facilitate dependency tracking and
> type verification, etc...  In order to do this the input needs to be
> stripped of all comments and newlines/control-characters so that only
> syntactically meaningful content remains.

I see your point and now understand the reason why PostgreSQL behaves the
way it does, that is: stripping the comments from the argument list, storing
the comments within the function body.

> The fundamental issue is that the input arguments to a function are
> stored as an array on pg_proc and thus to do not have their own OID
> with which to link onto pg_description.
>
> The fundamental question is by what means do you expect to be able to
> view and/or modify these comments?

As PostgreSQL silently accepts these comments in the argument list, I
expected them to be retrievable later on, not caring how and where they are
stored, but believing that they are stored.
I fully understand that for the ease of parsing and having a "usable"
argument list, PostgreSQL stores the argument list internally differently
than the argument list is presented to the user and I don't dare to argue
about it, because I accept the fact and assume it's not only to due
performance, but also to be able to use this meta information in the
database dictionary to write queries about it (something I appreciate!).

[[[ PostgreSQL recognizes that after removing the comments and associated
newlines that the rest of your command is still valid; but it doesn't
"accept" the comments per-se.  The ONLY comments that PostgreSQL "accepts"
are those created via "COMMENT ON" and those comments must be associated
with an OID/Object.  The comments in your function body are not accessible
from any PostgreSQL provided API but they remain simply because the entire
function body is treated as a single string with only syntax validation
performed before the record is committed. ]]]

> Feel free to provide thoughts and suggestions regarding how core could
> be modified to fit your intended use-case but I would offer that
> unless you are willing to fund and/or do the work that it isn't going
> to get much attention due apparent need to modify the catalogs and
> introduce a total new way of dealing with comments.  It is not the
> current policy of PostgreSQL to capture and store original DDL but
> instead it parsers the DDL into the needed catalog entries and then
recombines the entries into a "normalized"
> form when necessary (e.g., for pg_dump).

Well, first of all it was important to me to know that in fact I did the
right thing and PostgreSQL behaves as expected, not making an obvious
mistake on the syntax of comments.
Futhermore, now that I know its the expected way, I know how to circumvent
it, looking for other means of documenting the argument list in a sound way,
like including the comments at the head of the function's body, like I
already did.

Second, not knowing about the internals of the comment system of PostgreSQL
and how argument lists are handled: -

How about having something like:

I) COMMENT ON <function> (arglist-qualifier).<argumentname> IS '<comment>';
(or something similar)

and

II) Having PostgreSQL issuing a NOTICE that "comments get stripped" if you
use comments somewhere where PostgreSQL accepts them, but discards them
silently?

Best regards,
  Ralph

]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]

I) Makes sense but given that no existing code uses that syntax the
necessary benefit needed to overcome the overhead of introducing new syntax
is considerable.

II) Not going to happen and really isn't needed.  The rule is that "--"
comments are ALWAYS silently discarded (i.e., there is no mechanism to
retrieve those comments via database functions and/or views).  The way the
function body is stored is no different than if I write " INSERT INTO
testable (col1) VALUES ('-- comment'); " - testable.col1 now has a literal
value of "-- comment" stored within it because it is content and not a
comment.  The reason why it looks like a comment within a function body is
that the function body value is used by an execution/evaluation engine to
actually run internal code and that process - not the "CREATE FUNCTION"
interpreter - is the one that interprets that particular string sequence as
a comment.

David J.

On Friday, January 20, 2012 12:24:26 pm Ralph Graulich wrote:
> Hi David,

>
> II) Having PostgreSQL issuing a NOTICE that "comments get stripped" if you
> use comments somewhere where PostgreSQL accepts them, but discards them
> silently?

I think that is covered by this:

http://www.postgresql.org/docs/9.0/interactive/sql-syntax-lexical.html#SQL-SYNTAX-COMMENTS

"A comment is removed from the input stream before further syntax analysis and is effectively replaced by whitespace."

>
> Best regards,
>   Ralph

--
Adrian Klaver
adrian.klaver@gmail.com

Hi Adrian,

>> II) Having PostgreSQL issuing a NOTICE that "comments get stripped" if you
>> use comments somewhere where PostgreSQL accepts them, but discards them
>> silently?
>
> I think that is covered by this:
> http://www.postgresql.org/docs/9.0/interactive/sql-syntax-lexical.html#SQL-SYNTAX-COMMENTS
> "A comment is removed from the input stream before further syntax analysis and is effectively replaced by
whitespace."

Now that you mention it and I read that paragraph, it is obvious. I was far too focused on the chapters on COMMENT ON
andCREATE FUNCTION, so I didn't think about the possibility that there might be a paragraph on in-line comments, too
---as the syntax is well-known and so to say standard to nearly everyone who writes code, so it comes automagically
withouthaving to think about it (or read...). So it was clearly my fault. 

Best regards,
  Ralph