Berend Tober wrote on 17.01.2015 19:05:
> I often work with the output of pg_restore from a custom format dump
> file. For example a file produced by running
>
> pg_restore -s -1 -L listfile dumpfile
>
> where listfile has been edited to comment out most of the rows to
> leave only the data base objects I'm currently interested in.
>
> Most often, I'm refactoring functions and so don't really want to
> drop the function but rather want to do a "create or replace
> function" operation to implement the changes. Consequently I have to
> frequently do a global search and replace along the lines of
>
>
> sed -ie 's/CREATE FUNCTION/CREATE OR REPLACE FUNCTION/'
>
>
> I am not seeing in the documentation an option to generate the script
> with anything but straight "create function" commands.
>
> Is there a way for me to access this functionality (i.e., to generate
> "create or replace function" scripts) from the command line?
>
To me this sounds as if you are doing it the wrong way round.
To manage (refactor) your functions, you should have the current code stored
in a version control system, update the code there an then apply it to the
target database.
Extracting the code from the database in order to do refactoring is
like disassembling a program each time you want to apply a bugfix.
The code in the vcs would then contain the necessary "create or replace"
(btw you still need to drop the function if you change the parameters)
There are several tools that will help you manage the SQL scripts.
Were are quite content with using Liquibase, but Flyways is another alternative.
Thomas
