On 01/17/2015 10:05 AM, Berend Tober wrote:
> 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?
Not that I know of. Though it should be noted that what you can do with
CREATE OR REPLACE depends a good deal on what constitutes refactoring.
Per the docs:
http://www.postgresql.org/docs/9.3/interactive/sql-createfunction.html
"To replace the current definition of an existing function, use CREATE
OR REPLACE FUNCTION. It is not possible to change the name or argument
types of a function this way (if you tried, you would actually be
creating a new, distinct function). Also, CREATE OR REPLACE FUNCTION
will not let you change the return type of an existing function. To do
that, you must drop and recreate the function. (When using OUT
parameters, that means you cannot change the types of any OUT parameters
except by dropping the function.)"
My solution to this is using scripts for objects and keeping them under
version control. Lately I have been using Sqitch(sqitch.org/). There is
a learning curve, but I am finding it useful.
>
> I suppose I could pipe the pg_restore output through the sed command
> just as a matter of standard operating procedure, but the capability
> must exist because that is the way the scripts appear in pgadmin. I
> generally do not use the GUI tool and so would like it to happen
> automatically when using the command line tools.
>
> ---
> This email is free from viruses and malware because avast! Antivirus
> protection is active.
> http://www.avast.com
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
