10

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.

Jin Kim
  • 16,562
  • 18
  • 60
  • 86

3 Answers3

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

Community
  • 1
  • 1
Mike Buland
  • 631
  • 4
  • 5
  • 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. – Jin Kim Jan 20 '12 at 16:45
  • 6
    Ah, I misunderstood, for newer postgres try: \ef – Mike Buland Jan 20 '12 at 16:54
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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
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.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
Kuberchaun
  • 29,160
  • 7
  • 51
  • 59
  • 1
    I appreciate your candor and will take your criticisms into consideration although I think you are reading too much into my question. – Jin Kim Jan 20 '12 at 17:07
  • 1
    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. – Kuberchaun Jan 20 '12 at 17:34