PostgreSQL: how to export function definition in SQL?

Question

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

I think one way to do this is to dump the function definition into an SQL file, edit the SQL file, and then replace the definition in the database with the edited version.

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

What I did 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 that it looks like a function declaration, but it takes a lot of time and I wonder if Is there a smoother way to do this.

I am using PostgreSQL 9.1 if that matters.

EDIT:

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

Answer 1

This is really stated in the 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 of stored functions that reference a table in PostgreSQL

You should be able to use this on the command line or with the client to read the current proc text and do whatever you need :)

I hope this helps

Answer 2

You will also need 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 unique for functions with parameters:

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

pgAdmin, , . SQL script . .

Answer 3

, . ( ). , Git Mercurial, . psql , , . . . , , , . , , , , , , , . , , . - , , .