Skip site navigation (1) Skip section navigation (2)

Re: information schema parameter_default implementation

From: Ali Dar <ali(dot)munir(dot)dar(at)gmail(dot)com>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: information schema parameter_default implementation
Date: 2013-01-31 13:59:01
Message-ID: CAAj60S6AKHuAXem5URTBz5T_0RBhC1OOroWn7jsVZTbb=gme3g@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Wed, Jan 9, 2013 at 4:28 PM, Peter Eisentraut <peter_e(at)gmx(dot)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(at)postgresql(dot)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

In response to

Responses

pgsql-hackers by date

Next:From: Ali DarDate: 2013-01-31 14:02:43
Subject: Re: information schema parameter_default implementation
Previous:From: Zoltán BöszörményiDate: 2013-01-31 13:55:14
Subject: Re: Strange Windows problem, lock_timeout test request

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group