Thread: 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'::charactervarying, '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 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) > > 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 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)
>>
>>
> 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 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) >>> >>> >> 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