Postgres - Trying to backup Functions only

Question

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?

Answer 1

There are at least two problems with this:

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

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

3. 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).

Answer 2

I was faced to the same issue today and after some workaround, I excluded this "postgis" function from my query and got the result (where proname <> 'st_extent'). Hope that help

Answer 3

Error: "st_extent" is an aggregate function

ST_Extent is a PostGIS function. That means it's version-controlled in the Extension system. To add that function back, just install any version of PostGIS. When you back up, you don't and can't backup PostGIS because PostGIS is provided by sfcal, and geos which are compiled C++ libraries: st_extent is calling an internal compiled function. When you pg_dump you should get the CREATE EXTENSION postgis;. If that failed, it's likely that your system doesn't have PostGIS installed and you need to install it and reload the dump (so you get the things that depended on PostGIS too).