usability of pg_get_function_arguments
I am trying to extract function argument information using the
pg_get_function_arguments() and it strikes me that despite of this
function generating very useful information, it is actually not so user
friendly.
Consider the following:
-------------------------------------------------
create or replace function f13(int=10,varchar='hello',inout complex
timestamp='01-01-2009'::timestamp,varchar='comma here ,') as
$$
begin
end;
$$
language plpgsql;
where the pg_get_function_arguments generates the following string:
-------------------------------------------------
"integer DEFAULT 10, character varying DEFAULT 'hello'::character
varying, INOUT complex timestamp without time zone DEFAULT '2009-01-01
00:00:00'::timestamp without time zone, character varying DEFAULT 'comma
here ,'::character varying" (good luck parsing that, I thought)
-------------------------------------------------
In order to make the data above usable, one has to write a custom parser
to hopefully be able to make any use of the return data. Of course
another option is to parse the pg_proc.proargdefaults
which in turn is a challenge on its own.
Perhaps it would be much better if pg_get_function_arguments returned
the data is some kind of a structure than a blob of string like the above.
BTW: The same goes for pg_get_function_identity_arguments.
Any thoughts?
--
Regards,
Gevik
Gevik Babakhani <pgdev@xs4all.nl> writes:
Perhaps it would be much better if pg_get_function_arguments returned
the data is some kind of a structure than a blob of string like the above.
That would be more work, not less, for the known existing users of the
function (namely pg_dump and psql). It's a bit late to be redesigning
the function's API anyway.
In order to make the data above usable, one has to write a custom parser
to hopefully be able to make any use of the return data. Of course
another option is to parse the pg_proc.proargdefaults
which in turn is a challenge on its own.
The recommended way to do that is to use pg_get_expr --- it'd certainly
be a bad idea to try to parse that string from client code.
I experimented with your example and noticed that pg_get_expr requires a
hack --- it insists on having a relation OID argument, because all
previous use-cases for it involved expressions that might possibly refer
to a particular table. So you have to do something like
regression=# select pg_get_expr(proargdefaults,'pg_proc'::regclass) from pg_proc where proname='f13';
pg_get_expr
-----------------------------------------------------------------------------------------------------------------------
10, 'hello'::character varying, '2009-01-01 00:00:00'::timestamp without time zone, 'comma here ,'::character varying
(1 row)
where it doesn't matter which table you name, as long as you name one.
It would probably be cleaner to allow pg_get_expr to accept a zero OID,
for use when you are asking it to deparse an expression that's expected
to be Var-free.
regards, tom lane
That would be more work, not less, for the known existing users of the
function (namely pg_dump and psql). It's a bit late to be redesigning
the function's API anyway.
I agree.
The recommended way to do that is to use pg_get_expr --- it'd certainly
be a bad idea to try to parse that string from client code.
I experimented with your example and noticed that pg_get_expr requires a
hack --- it insists on having a relation OID argument, because all
previous use-cases for it involved expressions that might possibly refer
to a particular table. So you have to do something likeregression=# select pg_get_expr(proargdefaults,'pg_proc'::regclass) from pg_proc where proname='f13';
pg_get_expr
-----------------------------------------------------------------------------------------------------------------------
10, 'hello'::character varying, '2009-01-01 00:00:00'::timestamp without time zone, 'comma here ,'::character varying
(1 row)
Unfortunately, there is no way to know to which argument(s) the values
above belongs to.
After some searching, it looks like PgAdmin does the trick by hand
parsing the string.
Fortunately the result of pg_get_expr from above is ordered --- Perhaps
by doing some find and replace, I can determine to which argument the
returned default value belongs to.
Thank you for your help :)
--
Regards,
Gevik
Gevik Babakhani <pgdev@xs4all.nl> writes:
I experimented with your example and noticed that pg_get_expr requires a
hack --- it insists on having a relation OID argument, because all
previous use-cases for it involved expressions that might possibly refer
to a particular table. So you have to do something likeregression=# select pg_get_expr(proargdefaults,'pg_proc'::regclass) from pg_proc where proname='f13';
pg_get_expr
-----------------------------------------------------------------------------------------------------------------------
10, 'hello'::character varying, '2009-01-01 00:00:00'::timestamp without time zone, 'comma here ,'::character varying
(1 row)Unfortunately, there is no way to know to which argument(s) the values
above belongs to.
The last ones --- you can only omit arguments from the right, so it
makes no sense to allow a nonconsecutive set of defaults.
regards, tom lane
Tom Lane wrote:
Gevik Babakhani <pgdev@xs4all.nl> writes:
I experimented with your example and noticed that pg_get_expr requires a
hack --- it insists on having a relation OID argument, because all
previous use-cases for it involved expressions that might possibly refer
to a particular table. So you have to do something likeregression=# select pg_get_expr(proargdefaults,'pg_proc'::regclass) from pg_proc where proname='f13';
pg_get_expr
-----------------------------------------------------------------------------------------------------------------------
10, 'hello'::character varying, '2009-01-01 00:00:00'::timestamp without time zone, 'comma here ,'::character varying
(1 row)Unfortunately, there is no way to know to which argument(s) the values
above belongs to.The last ones --- you can only omit arguments from the right, so it
makes no sense to allow a nonconsecutive set of defaults.regards, tom lane
Indeed. I did not see that earlier. Thank you.
--
Regards,
Gevik