"ERROR: "array_agg" is an aggregate function" while getting function definitions

Question

I have the following query to retrieve function definitions:

select pg_get_functiondef
from (
   select pp.proname, pl.lanname,pn.nspname, pg_get_functiondef(pp.oid) 
   from pg_proc pp
   inner join pg_namespace pn on (pp.pronamespace = pn.oid)
   inner join pg_language pl on (pp.prolang = pl.oid)
   where pl.lanname = 'plpgsql' and 
   pn.nspname = 'pga'
   ) f
where pg_get_functiondef like '%userid%'

I just need functions containing a certain text in the definition.

When I run it, this error is thrown:

"ERROR:  "array_agg" is an aggregate function"

If I run just the inner query, it returns all the definitions as expected. No error.

I have also tried this where clause:

where pg_get_functiondef(pp.oid) like '%userid%'

But doesn't work either. How to do it?

Eric Hanson · Accepted Answer · 2019-02-06 06:18:40Z

6

pg_get_functiondef() does not work on aggregate functions, and pg_catalog.pg_proc contains a number of aggregate functions. You can filter them out using the pg_proc.proisagg boolean, as follows:

select
    proname as name,
    pronamespace::pg_catalog.regnamespace,
    pg_catalog.pg_get_functiondef(oid)
from pg_catalog.pg_proc
where proisagg is false; -- this filters them out

answered Feb 6, 2019 at 6:18

Eric Hanson

6541 gold badge7 silver badges10 bronze badges

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

1 Comment

user5480949 Over a year ago

Since v11 proisagg was replaced with prokind. I use prokind != 'a'

Erwin Brandstetter · Accepted Answer · 2019-02-21 00:54:50Z

3

Here's the answer:

SELECT n.nspname AS schema_name
     , p.proname AS function_name
     , pg_get_function_arguments(p.oid) AS args
     , pg_get_functiondef(p.oid) AS func_def
FROM  (SELECT oid, * FROM pg_proc p WHERE NOT p.proisagg) p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname LIKE 'pga%'
AND    pg_get_functiondef(p.oid) LIKE '%userid%';

Taken from this related answer on dba.SE and adapted to my needs:

PostgreSQL: How to list all stored functions that access specific table

edited Feb 21, 2019 at 0:54

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

answered Feb 9, 2018 at 16:07

EricBlair1984

3791 gold badge6 silver badges17 bronze badges

Comments

influent · Accepted Answer · 2024-03-12 17:23:09Z

0

None of the other solutions here worked for me but I got this working, on Postgres 14.9 on RDS.

SELECT n.nspname AS schema_name
     , p.proname AS function_name
     , pg_get_function_arguments(p.oid) AS args
     , pg_get_functiondef(p.oid) AS func_def
FROM  (SELECT * FROM pg_proc p2 WHERE NOT p2.prokind = 'a') p
JOIN   pg_namespace n ON n.oid = p.pronamespace
where n.nspname != 'pg_catalog'
and n.nspname != 'information_schema'
and pg_get_functiondef(p.oid) like '%searchtext%'
order by 1, 2;

answered Mar 12, 2024 at 17:23

influent

1,4073 gold badges17 silver badges33 bronze badges

Collectives™ on Stack Overflow

"ERROR: "array_agg" is an aggregate function" while getting function definitions

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related