Postgres Pro
Downloads
Home   >   mailing lists

Thread: Query function arg data types ONLY (no arg names)

Query function arg data types ONLY (no arg names)

From
Jeremy Finzel
Date:
It appears that neither pg_get_function_arguments nor pg_get_function_identity_arguments could be used for this.  I want to get function argument data types from the catalog by ordinal position, without the argument name.

For example, I want the same information for these 2 functions:

foo(p_1 int, p_2 text)

- {int, text}

foo(int, text)

- {int, text}

Any suggestions as to how to use the catalogs or built-in postgres functions to query this?

Thank you!
Jeremy

Re: Query function arg data types ONLY (no arg names)

From
Pavel Stehule
Date:
Hi

2018-04-28 18:52 GMT+02:00 Jeremy Finzel <finzelj@gmail.com>:
It appears that neither pg_get_function_arguments nor pg_get_function_identity_arguments could be used for this.  I want to get function argument data types from the catalog by ordinal position, without the argument name.

For example, I want the same information for these 2 functions:

foo(p_1 int, p_2 text)

- {int, text}

foo(int, text)

- {int, text}

Any suggestions as to how to use the catalogs or built-in postgres functions to query this?

 CREATE OR REPLACE FUNCTION public.foo(a integer, b integer, c text)
 RETURNS text
 LANGUAGE sql
AS $function$ select 'hi'; $function$

postgres=# select (proargtypes::regtype[])[0:] from pg_proc where proname = 'foo';
┌─[ RECORD 1 ]┬────────────────────────┐
│ proargtypes │ {integer,integer,text} │
└─────────────┴────────────────────────┘

Regards

Pavel


Thank you!
Jeremy

Re: Query function arg data types ONLY (no arg names)

From
Tom Lane
Date:
Jeremy Finzel <finzelj@gmail.com> writes:
> It appears that neither pg_get_function_arguments
> nor pg_get_function_identity_arguments could be used for this.  I want to
> get function argument data types from the catalog by ordinal position,
> without the argument name.

Something involving pg_proc.proargtypes::regtype[] might be useful.

            regards, tom lane

Re: Query function arg data types ONLY (no arg names)

From
Jeremy Finzel
Date:

On Sat, Apr 28, 2018 at 12:01 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi

2018-04-28 18:52 GMT+02:00 Jeremy Finzel <finzelj@gmail.com>:
It appears that neither pg_get_function_arguments nor pg_get_function_identity_arguments could be used for this.  I want to get function argument data types from the catalog by ordinal position, without the argument name.

For example, I want the same information for these 2 functions:

foo(p_1 int, p_2 text)

- {int, text}

foo(int, text)

- {int, text}

Any suggestions as to how to use the catalogs or built-in postgres functions to query this?

 CREATE OR REPLACE FUNCTION public.foo(a integer, b integer, c text)
 RETURNS text
 LANGUAGE sql
AS $function$ select 'hi'; $function$

postgres=# select (proargtypes::regtype[])[0:] from pg_proc where proname = 'foo';
┌─[ RECORD 1 ]┬────────────────────────┐
│ proargtypes │ {integer,integer,text} │
└─────────────┴────────────────────────┘

Regards

Pavel


Thank you!
Jeremy

This is perfect - thank you!