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'
" > /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 "restore" 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).

StackExchange Database Administrators Q#141174, answer score: 4