Welcome to End Point’s blog

Ongoing observations by End Point people

PostgreSQL tip: using pg_dump to extract a single function

A common task that comes up in PostgreSQL is the need to dump/edit a specific function. While ideally, you're using DDL files and version control (hello, git!) to manage your schema, you don't always have the luxury of working in such a controlled environment. Recent versions of psql have the \ef command to edit a function from within your favorite editor, but this is available from version 8.4 onward only.

An alternate approach is to use the following invocation:

  pg_dump -Fc -s | pg_restore -P 'funcname(args)'

The -s flag is the short form of --schema-only; i.e., we don't care about wasting time/space with the data. -P tells pg_restore to extract the function with the following signature.

As always, there are some caveats: the function name must be spelled out explicitly using the full types as they occur in the dump's custom format (i.e., you must use 'foo_func(integer)' instead of 'foo_func(int)'). You can always see a list of all of the available functions by using the command:

  pg_dump -Fc -s | pg_restore -l | grep FUNCTION


Pavel Stěhule said...

You can use more simple solution (needs 8.3)

SELECT pg_catalog.pg_get_functiondef('name')

David Christensen said...

Hi Pavel,

I did not see this function in 8.3, but it does sound useful. (Tested using 8.3.9.) It looks like it may have been introduced in 8.4? (Yup, verified.)



Pavel Stěhule said...

sorry, it's for 8.4

ferryanto situmorang said...

thanks, it works for me.

Selena Deckelmann said...

For future travelers, the proper incantation to get a function definition is:

SELECT pg_catalog.pg_get_functiondef('name'::regproc);

And if you want it to print out nicely while using psql, try: '\x \a' just before you run the SELECT.

Mark Johnson said...

I've always used "\df+ name" after setting unaligned format (using \a).

Ross Reedstrom said...

And for even later travelers, combine all of \a \t and \g
in place of \a \x, don't forget `\g myfunc.sql` instead of ;, i.e.

SELECT pg_catalog.pg_get_functiondef('name'::regproc) \g name.sql

stores a reloadable definition to `name.sql`