Postgres - Trying to backup Functions only I'm trying to follow Craig Ringer's answer to this question:
https://stackoverflow.com/questions/13758003/how-to-take-backup-of-functions-only-in-postgres

My command is:

psql -U username -AT db_name -c "
    SELECT pg_get_functiondef(f.oid) 
    FROM pg_catalog.pg_proc f 
    INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid) 
    WHERE n.nspname = 'public'
" &gt; /dump/file/functions.sql


But I'm getting the following error:


Error: "st_extent" is an aggregate function



Why doesn't it like aggregate functions? And what can I do to get around this problem?
There are at least two problems with this:

Don't include functions installed by an extension. Just install the extension to &quot;restore&quot; that function to any other database.

Aggregate functions are completely different and have to be treated separately. The CREATE AGGREGATE syntax differs from CREATE FUNCTION.

Don't you want to include the COMMENT for each function? (If one exists.)


I suggest these two queries:
Functions
-- SET LOCAL search_path = '';  -- force schema-qualification everywhere
SELECT pg_get_functiondef(p.oid) AS function
     , 'COMMENT ON FUNCTION ' || p.oid::regprocedure
       || ' IS ' || quote_literal(obj_description(p.oid, 'pg_proc')) AS comment
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname !~ '^(pg_|information_schema)'    -- exclude system schemas
-- AND    n.nspname =  ...                         -- or from selected schema only?
AND    NOT EXISTS (                                -- exclude functions from extensions
   SELECT FROM pg_depend 
   WHERE  objid = p.oid 
   AND    deptype = 'e'
   )
--    AND    NOT p.proisagg                        -- exclude aggregate functions! for pg 10 or older
AND    NOT p.prokind = 'f'                         -- only normal functions! pg 11+
ORDER  BY n.nspname, p.proname;

Various types of dependencies are stored in the system catalog pg_depend. deptype = 'e' indicates it's part of an extension.
About proisagg vs prokind:

PostgreSQL 11 error: column p.proisagg does not exist

Aggregates (= aggregate functions)
-- SET LOCAL search_path = '';  -- force schema-qualification everywhere
SELECT format('CREATE AGGREGATE %s (SFUNC = %s, STYPE = %s%s%s%s%s)'
            , a.aggfnoid::regprocedure
            , a.aggtransfn
            , a.aggtranstype::regtype
            , ', SORTOP = '    || NULLIF(a.aggsortop, 0)::regoper
            , ', INITCOND = '  || a.agginitval
            , ', FINALFUNC = ' || NULLIF(a.aggfinalfn, 0)
            , CASE WHEN a.aggfinalextra THEN ', FINALFUNC_EXTRA' END
            --  add more to cover special cases like moving-aggregate etc.
              ) AS aggregate
     , 'COMMENT ON AGGREGATE ' || aggfnoid::regprocedure
       || ' IS ' || quote_literal(obj_description(aggfnoid, 'pg_proc')) AS comment
FROM   pg_aggregate a
JOIN   pg_proc      p ON p.oid = a.aggfnoid
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname !~ '^(pg_|information_schema)'    -- exclude system schemas
-- AND    n.nspname = 'public'                     -- or from selected schema only?
AND    NOT EXISTS (
   SELECT FROM pg_depend 
   WHERE  objid = a.aggfnoid
   AND    deptype = 'e'
   )
ORDER  BY n.nspname, p.proname;

This only covers plain aggregate functions. Further reading:

How to get definition/source code of an aggregate in PostgreSQL?

To make it complete, you might want want add ownership (ALTER FUNCTION ... OWNER TO ...;) and privileges (GRANT / REVOKE).