CODEWITHSUNDEEP
postgresqlpostgresql-9.1
Jin Kim
Jin Kim

Reputation: 17722

PostgreSQL: How do I export a function definition to SQL

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.

Upvotes: 10

Views: 16765

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656231

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.

Upvotes: 3

Kuberchaun
Kuberchaun

Reputation: 30314

I think you need to take a step back and see the root issue here. Which is you're not using version control to version your files (database objects). There are plenty of free ones, Git and Mercurial to name a few. So use psql or the query Mike provided to dump the structure out and put them in version control. Going forward check out from version control and make edits there. You should be deploying code from this version control system to the database server. It is also useful to reconcile that the code you have in version control matches the code in the database in a automated and on a regular basis. In theory though if a strict process is in place code that isn't checked into source control should never make it to the database and you don't ever have to wonder if source control matches what the database has. However I don't trust that people with admin access won't abuse their privilege so I put steps in place to check this. If someone is found to be abusing their privileges that can be dealt with in other ways.

Upvotes: 0

Mike Buland
Mike Buland

Reputation: 631

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

Upvotes: 6

Related Questions