Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
How to describe functions.
0 views
Skip to first unread message
Mladen Gogala
Dec 10, 2009, 11:13:05 AM12/10/09
to
I would like to write a single query listing all of the function argument
types. I know that I can write a procedure but I would prefer a single
query. The problem is that in pg_proc, the column proargtypes is of the
type "oidvector", which is another name for an array oid[]. The types
are, of course in pg_type table. The problem here is the array handling.
what I would really like is something like this:
types. I know that I can write a procedure but I would prefer a single
query. The problem is that in pg_proc, the column proargtypes is of the
type "oidvector", which is another name for an array oid[]. The types
are, of course in pg_type table. The problem here is the array handling.
what I would really like is something like this:
select p.proname,(for($i=0;$i<p.pronargs;$i++ { t.typename where
t.oid=p.proargtypes[$i] }
from pg_proc p, pg_type t;
Of course, I am aware that this is a bad syntax but this pseudocode is
the best way to describe what I want to achieve. The issue is the array
handling.
Laurenz Albe
Dec 11, 2009, 3:02:58 AM12/11/09
to
You mean something like that?
SELECT
p.proname,
(SELECT array_agg(t.typname)
FROM unnest(p.proargtypes) AS o(oid) JOIN
pg_type AS t ON (o.oid=t.oid)) AS argtypes
FROM pg_proc AS p;
Yours,
Laurenz Albe
Thomas Kellerer
Dec 11, 2009, 3:22:37 AM12/11/09
to
Laurenz Albe, 11.12.2009 09:02:
> Mladen Gogala wrote:
>> I would like to write a single query listing all of the function argument
>> types. I know that I can write a procedure but I would prefer a single
>> query. The problem is that in pg_proc, the column proargtypes is of the
>> type "oidvector", which is another name for an array oid[]. The types
>> are, of course in pg_type table. The problem here is the array handling.
>> what I would really like is something like this:
>>
> Mladen Gogala wrote:
>> I would like to write a single query listing all of the function argument
>> types. I know that I can write a procedure but I would prefer a single
>> query. The problem is that in pg_proc, the column proargtypes is of the
>> type "oidvector", which is another name for an array oid[]. The types
>> are, of course in pg_type table. The problem here is the array handling.
>> what I would really like is something like this:
>>
> You mean something like that?
>
> SELECT
> p.proname,
> (SELECT array_agg(t.typname)
> FROM unnest(p.proargtypes) AS o(oid) JOIN
> pg_type AS t ON (o.oid=t.oid)) AS argtypes
> FROM pg_proc AS p;
>
> Yours,
> Laurenz Albe
>
>
> SELECT
> p.proname,
> (SELECT array_agg(t.typname)
> FROM unnest(p.proargtypes) AS o(oid) JOIN
> pg_type AS t ON (o.oid=t.oid)) AS argtypes
> FROM pg_proc AS p;
>
> Yours,
> Laurenz Albe
>
If you are on 8.4 you can use:
SELECT proname,
pg_catalog.pg_get_function_arguments(oid)
FROM pg_proc;
Thomas
Mladen Gogala
Dec 11, 2009, 5:22:26 PM12/11/09
to
0 new messages