Re: Can pg_restore produce create or replace commands
От | Adrian Klaver |
---|---|
Тема | Re: Can pg_restore produce create or replace commands |
Дата | |
Msg-id | 54BAB6F7.1040807@aklaver.com обсуждение исходный текст |
Ответ на | Can pg_restore produce create or replace commands (Berend Tober <btober@broadstripe.net>) |
Ответы |
Re: Can pg_restore produce create or replace commands
(Berend Tober <btober@broadstripe.net>)
|
Список | 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
В списке pgsql-general по дате отправления: