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.

Community · Accepted Answer · 2017-05-23 12:17:20Z

6

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

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Jan 20, 2012 at 16:34

Mike Buland

7314 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jin Kim Over a year ago

Thanks for your answer but it doesn't give me more than what \df+ already does. I guess I was hoping for something that would include the complete SQL, including "CREATE OR REPLACE FUNCTION ...(...)" part so I don't have to massage the output every time.

Mike Buland Over a year ago

Ah, I misunderstood, for newer postgres try: \ef <procname>

Erwin Brandstetter · Accepted Answer · 2012-05-31 11:43:37Z

3

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.

edited May 31, 2012 at 11:43

answered Jan 20, 2012 at 17:50

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

1 Comment

Erwin Brandstetter Over a year ago

@Fëanor: I removed the screenshot, it's not needed. Simplified query, too.

Jonathan Hall · Accepted Answer · 2012-01-20 17:00:48Z

0

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.

edited Jan 20, 2012 at 17:00

Jonathan Hall

80.5k19 gold badges162 silver badges206 bronze badges

answered Jan 20, 2012 at 16:37

Kuberchaun

30.5k8 gold badges54 silver badges59 bronze badges

2 Comments

Jin Kim Over a year ago

I appreciate your candor and will take your criticisms into consideration although I think you are reading too much into my question.

Kuberchaun Over a year ago

In context to your question I agree. However if you and I worked together and this came up the answer I would give you is the answer I submitted here. I hate working around upstream deficiencies. Maybe in your case that is a little harsh as I don't know if you are just stuck with a system with these deficiencies and you need to work around them for the time being.

Collectives™ on Stack Overflow

PostgreSQL: How do I export a function definition to SQL

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related