PostgreSQL: How do I export a function definition to SQL

Question

I have a function (stored procedure) defined in a database that I would like to edit.

I think one way of doing this is to dump the function definition to a SQL file, edit the SQL file, then replace the definition in the database with the edited version.

Is it possible to do this (dump the definition to a SQL file)?

What I have been doing in the past is to use psql to connect to the database, run /df+ function, copy the output to a text file, massage the text so it looks like a function declaration, but this is time consuming and I'm wondering if there is a sleeker way of doing it.

I am using PostgreSQL 9.1 if it matters.

EDIT:

I accepted Mike Buland's answer because he provided the correct answer in his comment, which was to run \ef function in psql.

Answer 1

This is actually listed in a previous question:

SELECT  proname, prosrc
FROM    pg_catalog.pg_namespace n
JOIN    pg_catalog.pg_proc p
ON      pronamespace = n.oid
WHERE   nspname = 'public';

List stored functions that reference a table in PostgreSQL

You should be able to use this on the command line or with a client to read the current text of the proc and do anything you'd like with it :)

I hope that helps

Answer 2

You would also need the function arguments:

SELECT p.proname
     , pg_catalog.pg_get_function_arguments(p.oid) as params
     , p.prosrc
FROM   pg_catalog.pg_proc p
WHERE  oid = 'myschema.myfunc'::regproc;

Or, to make it unambiguous for functions with parameters:

WHERE  oid = 'myschema.myfunc(text)'::regprocedure;

Or you can use pgAdmin to do what you describe a lot more comfortably. It displays the complete SQL script to recreate objects and has an option to copy that to the the edit window automatically. Edit and execute.