Re: Can pg_restore produce create or replace commands - Mailing list pgsql-general

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