Re: information schema parameter_default implementation

On Wed, Jan 9, 2013 at 4:28 PM, Peter Eisentraut <peter_e@gmx.net> wrote:

Here is an implementation of the
information_schema.parameters.parameter_default column.

I ended up writing a C function to decode the whole thing from the
system catalogs, because it was too complicated in SQL, so I abandoned
the approach discussed in [0].


[0]: http://archives.postgresql.org/message-id/1356092400.25658.6.camel@vanquo.pezone.net

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

I checked our your patch. There seems to be an issue when we have OUT parameters after the DEFAULT values. For example a simple test case is given below:

postgres=# CREATE FUNCTION functest1(a int default 1, out b int)

postgres-#     RETURNS int

postgres-#     LANGUAGE SQL

postgres-#     AS 'SELECT $1';

CREATE FUNCTION

postgres=# 

postgres=# SELECT ordinal_position, parameter_name, parameter_default FROM information_schema.parameters WHERE  specific_name LIKE 'functest%' ORDER BY 1;

 ordinal_position | parameter_name | parameter_default 

------------------+----------------+-------------------

                1 | a              | 1

                2 | b              | 1

(2 rows)

The out parameters gets the same value as the the last default parameter. The patch work only when default values are at the end. Switch the parameters and it starts working(make OUT parameter as first and default one the last one). Below is the example:

postgres=# CREATE FUNCTION functest1(out a int, b int default 1)

postgres-#     RETURNS int

postgres-#     LANGUAGE SQL

postgres-#     AS 'SELECT $1';

CREATE FUNCTION

postgres=# SELECT ordinal_position, parameter_name, parameter_default FROM information_schema.parameters WHERE  specific_name LIKE 'functest%' ORDER BY 1;

 ordinal_position | parameter_name | parameter_default 

------------------+----------------+-------------------

                1 | a              | 

                2 | b              | 1

(2 rows)

Some other minor observations:

1) Some variables are not lined in pg_get_function_arg_default().

2) I found the following check a bit confusing, maybe you can make it better

if (!argmodes || argmodes[i] == PROARGMODE_IN || argmodes[i] == PROARGMODE_INOUT || argmodes[i] == PROARGMODE_VARIADIC)

2) inputargn can be assigned in declaration.

3) Function level comment for pg_get_function_arg_default() is missing.

4) You can also add comments inside the function, for example the comment for the line:

nth = inputargn - 1 - (proc->pronargs - proc->pronargdefaults);

5) I think the line added in the documentation(informational_schema.sgml) is very long. Consider revising. Maybe change from:

"The default expression of the parameter, or null if none or if the function is not owned by a currently enabled role." TO

"The default expression of the parameter, or null if none was specified. It will also be null if the function is not owned by a currently enabled role."

I don't know what do you exactly mean by: "function is not owned by a currently enabled role"?

Regards,

Ali Dar