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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
