How to describe functions.

#1

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:

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.

--
http://mgogala.byethost5.com

#2

Mladen Gogala wrote:

Quote:

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:

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.

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

#3

Laurenz Albe, 11.12.2009 09:02:

Quote:

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

If you are on 8.4 you can use:

SELECT proname,
pg_catalog.pg_get_function_arguments(oid)
FROM pg_proc;

Thomas

#4

On Fri, 11 Dec 2009 09:02:58 +0100, Laurenz Albe wrote:

Quote:

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:

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.

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

Eh, yes, I was missing the "unnest" part. Thanks.

--
http://mgogala.byethost5.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

How to describe functions.

comp.databases.postgresql comp.databases.postgresql

How to describe functions. - 12-10-2009 , 11:13 AM

Re: How to describe functions. - 12-11-2009 , 03:02 AM

Re: How to describe functions. - 12-11-2009 , 03:22 AM

Re: How to describe functions. - 12-11-2009 , 05:22 PM