Thread: Can pg_restore produce create or replace commands
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? 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
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
Adrian Klaver wrote: > 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... >> >> 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"... >> >> 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. > ... > "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... Indeed. I have run into that occasionally. But currently and most often it has not been much problem as the refactoring is generally internal to the function behavior ... in fact most of them are trigger functions, and since I have adopted a consistent naming convention there are practically never function interface changes. > > 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. Oh sqitch is (looks to be) awesome and I wish so much to employ it, but I have not had success it getting it installed. I do use Wheeler's companion tool pgtap, and THAT is totally awesome as well and is making life SO much better. I cannot envision ever again doing data base development without it! I just wonder how PgAdmin creates the scripts, then. Is that a feature specific to the PgAdmin application then rather than the underlying system and tools?
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? > > 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. Not sure how pgAdmin does it. Just remembered something though, pg_get_functiondef(), available in 8.4+: http://www.postgresql.org/docs/9.3/interactive/functions-info.html "pg_get_functiondef returns a complete CREATE OR REPLACE FUNCTION statement for a function. pg_get_function_arguments returns the argument list of a function, in the form it would need to appear in within CREATE FUNCTION. pg_get_function_result similarly returns the appropriate RETURNS clause for the function. pg_get_function_identity_arguments returns the argument list necessary to identify a function, in the form it would need to appear in within ALTER FUNCTION, for instance. This form omits default values." So: test=# SELECT pg_get_functiondef('ean_substr'::regproc); pg_get_functiondef -------------------------------------------------------- CREATE OR REPLACE FUNCTION public.ean_substr(text) + RETURNS boolean + LANGUAGE plpgsql + AS $function$ + DECLARE + offset integer := 0; + -- Support UPCs. + ean TEXT := CASE WHEN length($1) = 12 THEN + '0' || $1 + ELSE + $1 + END; + BEGIN + -- Make sure we really have an EAN. + IF ean !~ '^\\d{13}$' THEN RETURN FALSE; END IF; + + RETURN 10 - ( + ( + -- Sum even numerals. + substring(ean, 2 + offset, 1)::integer + + substring(ean, 4 + offset, 1)::integer + + substring(ean, 6 + offset, 1)::integer + + substring(ean, 8 + offset, 1)::integer + + substring(ean, 10 + offset, 1)::integer + + substring(ean, 12 + offset, 1)::integer + ) * 3 -- Multiply total by 3. + -- Add odd numerals except for checksum (13).+ + substring(ean, 3 + offset, 1)::integer + + substring(ean, 5 + offset, 1)::integer + + substring(ean, 7 + offset, 1)::integer + + substring(ean, 9 + offset, 1)::integer + + substring(ean, 11 + offset, 1)::integer + -- Compare to the checksum. + ) % 10 = substring(ean, 12 + offset, 1)::integer; + END; + $function$ + (1 row) > > --- > This email is free from viruses and malware because avast! Antivirus > protection is active. > http://www.avast.com > > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver wrote:
> 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. ...
>>
>> 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"...
>
> Not sure how pgAdmin does it. Just remembered something though,
> pg_get_functiondef(), available in 8.4+:...
>
> test=# SELECT pg_get_functiondef('ean_substr'::regproc);
> ...
That has potential. However, in some instances the object I'm
refactoring will end up having dependencies, for instance for the case
of views oftentimes I will have to do the drop/create for it and all
dependent objects. In those situations, generating scripts from the
pg_restore output is very convenient, since it tells me all the
dependencies and I can adjust the listfile contents to get them all and
in the correct order.
At this point I'm thinking to wrap the pg_restore invocation in a script
that includes piping through sed to transform the create statements.
I would have thought that the functionality in PgAdmin and the command
line utilities would rely on the same underlying source code and so
support the same functionality. Alas.
---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com
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
Thomas Kellerer wrote: > Berend Tober wrote on 17.01.2015 19:05: >> I often work with the output of pg_restore from a custom format dump >> file. ... >> >> 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" ... >> > > To me this sounds as if you are doing it the wrong way round. Possibly. But if the revision control system and the production data base disagree, then which one do you believe? > > 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) > --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com
On 01/17/2015 06:01 PM, Berend Tober wrote: > Thomas Kellerer wrote: >> Berend Tober wrote on 17.01.2015 19:05: >>> I often work with the output of pg_restore from a custom format dump >>> file. ... >>> >>> 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" ... >>> >> >> To me this sounds as if you are doing it the wrong way round. > > > Possibly. But if the revision control system and the production data > base disagree, then which one do you believe? I guess this depends on what you see as disagree. It is entirely possible that the latest version(say testing) of code in the VCS is not the same as the code in the production database. That is what tags are for, a way to mark a point in time(development) to refer to. Having a tag in the VCS that matches a state of the production database allows one to make a comparison and be confident that the version control code is the correct code. This of course requires an agreed upon method of applying changes and tagging code. If you search -general you will find previous discussions on this, for example: http://www.postgresql.org/message-id/CAPTJ3=cJ5kB0Y9DUAA6rQh8yhqb5MssN1FvrFumGQLTOQ1+UPQ@mail.gmail.com > > >> >> 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) >> > > > > --- > This email is free from viruses and malware because avast! Antivirus > protection is active. > http://www.avast.com > > > -- Adrian Klaver adrian.klaver@aklaver.com