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.proargmodes,
          format_type(unnest(p.proallargtypes), NULL) AS proargtypes, -- and here
          p.proallargtypes,
          p.proargnames,
          pg_get_expr(p.proargdefaults, 0) AS prodefaults,
          format_type(p.prorettype, NULL) AS prorettype,
          l.lanname
        FROM pg_catalog.pg_proc p
        JOIN pg_catalog.pg_language l
        ON l.oid = p.prolang
        JOIN pg_catalog.pg_namespace n
        ON n.oid = p.pronamespace
        WHERE n.nspname = 'public'
) x
GROUP BY proname, prosrc, pronargs, proargmodes, proallargtypes, proargnames, prodefaults, prorettype, lanname

Pavel Stehule · Accepted Answer · 2013-11-26 19:18:30Z

10

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)

answered Nov 26, 2013 at 19:18

Pavel Stehule

46.7k6 gold badges104 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

AmShaegar Over a year ago

Thanks for that! I will test that and come back to you. Sadly, I cannot create my own function because I am writing a database independent tool, which can list functions. Just read access though.

AmShaegar Over a year ago

Awesome, I was aware of the problem with OUT arguments. All I needed to solve my problem was the ARRAY() function of your custom function. Thank you very much!

Pavel Stehule Over a year ago

@AmShaegar: just note - ARRAY() is not function - it is a "array constructor with subselect". Syntax is same, but semantic is little bit different :)

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related