Apply function to every element of an array in a SELECT statement

Question

I am listing all functions of a PostgreSQL schema and need the human readable types for every argument of the functions. OIDs of the types a represented as an array in `proallargtypes`. I can unnest the array and apply `format_type()` to it, which causes the query to split into multiple rows for a single function. To avoid that I have to create an outer `SELECT` to `GROUP` the argtypes again because, apperently, one cannot group an unnested array. All columns are dependent on proname but I have to list all columns in `GROUP BY` clause, which is unnecessary but proname is not a primary key. Is there a better way to achieve my goal of an output like this: ``` proname | ... | protypes ------------------------------------- test | ... | {integer,integer} ``` I am currently using this query: ``` SELECT proname, prosrc, pronargs, proargmodes, array_agg(proargtypes), -- see here proallargtypes, proargnames, prodefaults, prorettype, lanname FROM ( SELECT p.proname, p.prosrc, p.pronargs, p.proa…

Accepted Answer

you can use internal "undocumented" function pg_catalog.pg_get_function_arguments(p.oid).

```
postgres=# SELECT pg_catalog.pg_get_function_arguments('fufu'::regproc);
 pg_get_function_arguments 
---------------------------
 a integer, b integer
(1 row)
```

Now, there are no build "map" function. So unnest, array_agg is only one possible. You can simplify life with own custom function:

```
CREATE OR REPLACE FUNCTION format_types(oid[])
RETURNS text[] AS $$
   SELECT ARRAY(SELECT format_type(unnest($1), null))
$$ LANGUAGE sql IMMUTABLE;
```

and result

```
postgres=# SELECT format_types('{21,22,23}');
          format_types          
-------------------------------
{smallint,int2vector,integer}
(1 row)
```

Then your query should to be:

```
SELECT proname, format_types(proallargtypes)
   FROM pg_proc
  WHERE pronamespace = 2200 AND proallargtypes;
```

But result will not be expected probably, because proallargtypes field is not empty only when OUT parameters are used. It is empty usually. You should to look to proargtypes field, but it is a oidvector type - so you should to transform to oid[] first.

```
postgres=# SELECT proname, format_types(string_to_array(proargtypes::text,' ')::oid[])
              FROM pg_proc
             WHERE pronamespace = 2200
             LIMIT 10;
           proname            |                    format_types                    
------------------------------+----------------------------------------------------
 quantile_append_double       | {internal,"double precision","double precision"}
 quantile_append_double_array | {internal,"double precision","double precision[]"}
 quantile_double              | {internal}
 quantile_double_array        | {internal}
 quantile                     | {"double precision","double precision"}
 quantile                     | {"double precision","double precision[]"}
 quantile_cont_double         | {internal}
 quantile_cont_double_array   | {internal}
 quantile_cont                | {"double precision","double precision"}
 quantile_cont                | {"double precision","double precision[]"}
(10 rows)
```

Apply function to every element of an array in a SELECT statement

Solution

Problem