Re: Can pg_restore produce create or replace commands

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(dot)klaver(at)aklaver(dot)com