pg_dump option to dump only functions

Sean,

I was wonderring, because I create a lot of server side utility functions,
whether adding an option to pg_dump to just dump functions has been
considered. I did a quick perusal of the code, and noted that there is a
separate section within pg_dump to get the functions, but it is not able to
be triggered separately from schema and data. Any reason why this wouldn't
be a good(tm) idea?

It would be an *excellent* idea, along with options to dump specific
functions, and both specific and all views/types/operators. Go for it.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh@agliodbs.com> writes:

I was wonderring, because I create a lot of server side utility functions,
whether adding an option to pg_dump to just dump functions has been
considered. I did a quick perusal of the code, and noted that there is a
separate section within pg_dump to get the functions, but it is not able to
be triggered separately from schema and data. Any reason why this wouldn't
be a good(tm) idea?

It would be an *excellent* idea, along with options to dump specific
functions, and both specific and all views/types/operators. Go for it.

I kinda thought we had a TODO entry for that already, but I see we
don't.

Another thing you'd find yourself wanting very quickly is an option to
follow dependencies, ie "dump these objects plus everything they depend
on". Otherwise you'd have to find the dependencies manually, which
would get real tedious in any complex schema.

Proposed TODO entries for pg_dump:

* Allow selection of individual object(s) of all types, not just tables
* In a selective dump, allow dumping of all dependencies of the objects

regards, tom lane

On Sat, Oct 08, 2005 at 02:24:00PM -0700, Josh Berkus wrote:

Sean,

I was wonderring, because I create a lot of server side utility functions,
whether adding an option to pg_dump to just dump functions has been
considered. I did a quick perusal of the code, and noted that there is a
separate section within pg_dump to get the functions, but it is not able to
be triggered separately from schema and data. Any reason why this wouldn't
be a good(tm) idea?

It would be an *excellent* idea, along with options to dump specific
functions, and both specific and all views/types/operators. Go for it.

Agreed. IMHO this should actually tie in with the discussion on -general
right now about better ways to specify includes and excludes. ISTM that
pg_dump should allow you to feed it a file that specifies what you do
and don't want dumped. That can be extended to include object type. One
possibile file format, off the top of my head:

<action> <objects> <filter> <options>

<action>
Roughly, dump or ignore. Can be object-dependant. For example, tables
could have these actions:
dump-ddl dumps DDL only for tables matching <filter>
dump-data dumps data only for tables matching <filter>
dump-all dumps DDL and data for tables matching <filter>
ignore ignores tables matching <filter>

<objects>
List of object types, or * for any object. IE: tables,views,indexes

<filter>
Regex of what this rule applies to

<options>
Would be useful to allow specifying if dependancies should be dumped.
Maybe some other things as well.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Am Samstag, den 08.10.2005, 18:03 -0400 schrieb Tom Lane:

Josh Berkus <josh@agliodbs.com> writes:

I was wonderring, because I create a lot of server side utility functions,
whether adding an option to pg_dump to just dump functions has been
considered. I did a quick perusal of the code, and noted that there is a
separate section within pg_dump to get the functions, but it is not able to
be triggered separately from schema and data. Any reason why this wouldn't
be a good(tm) idea?

It would be an *excellent* idea, along with options to dump specific
functions, and both specific and all views/types/operators. Go for it.

I kinda thought we had a TODO entry for that already, but I see we
don't.

Another thing you'd find yourself wanting very quickly is an option to
follow dependencies, ie "dump these objects plus everything they depend
on". Otherwise you'd have to find the dependencies manually, which
would get real tedious in any complex schema.

Proposed TODO entries for pg_dump:

* Allow selection of individual object(s) of all types, not just tables
* In a selective dump, allow dumping of all dependencies of the objects

May I suggest the implementation of -l / -L like pg_restore has?
So you can work the same way to produce the list of objects
to dump and manipulate them - as well as adding the depencies
tracking option to pg_restore?

Regards
Tino

Tom Lane wrote:

Josh Berkus <josh@agliodbs.com> writes:

I was wonderring, because I create a lot of server side utility functions,
whether adding an option to pg_dump to just dump functions has been
considered. I did a quick perusal of the code, and noted that there is a
separate section within pg_dump to get the functions, but it is not able to
be triggered separately from schema and data. Any reason why this wouldn't
be a good(tm) idea?

It would be an *excellent* idea, along with options to dump specific
functions, and both specific and all views/types/operators. Go for it.

I kinda thought we had a TODO entry for that already, but I see we
don't.

Another thing you'd find yourself wanting very quickly is an option to
follow dependencies, ie "dump these objects plus everything they depend
on". Otherwise you'd have to find the dependencies manually, which
would get real tedious in any complex schema.

Proposed TODO entries for pg_dump:

* Allow selection of individual object(s) of all types, not just tables
* In a selective dump, allow dumping of all dependencies of the objects

Added to TODO.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tino Wildenhain wrote:

Proposed TODO entries for pg_dump:

* Allow selection of individual object(s) of all types, not just tables
* In a selective dump, allow dumping of all dependencies of the objects

May I suggest the implementation of -l / -L like pg_restore has?
So you can work the same way to produce the list of objects
to dump and manipulate them - as well as adding the depencies
tracking option to pg_restore?

Good idea, added:

o Add options like pg_restore -l and -L to pg_dump

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In what might be called my spare time, I was looking at pg_dump.c to see
about adding an option to dump only functions, and I think a comment got
pushed out of place in the section for handling arguments:

395 case 'X':
396 if (strcmp(optarg,
"disable-dollar-quoting") == 0)
397 disable_dollar_quoting = 1;
398 else if (strcmp(optarg,
"disable-triggers") == 0)
399 disable_triggers = 1;
400 else if (strcmp(optarg,
"use-set-session-authorization") == 0)
401 use_setsessauth = 1;
402 else
403 {
404 fprintf(stderr,
405 _("%s:
invalid -X option -- %s\n"),
406 progname,
optarg);
407 fprintf(stderr, _("Try
\"%s --help\" for more information.\n"), progname );
408 exit(1);
409 }
410 break;
411
412 case 'Z': /*
Compression Level */
413 compressLevel = atoi(optarg);
414 break;
415 /* This covers the long options
equivalent to -X xxx. */
^^^^^^^^^^^^^^^^ --------------
This comment seems out of place here. I imagine it once was after the break
for case: 'X': (line411) and got misplaced when case 'Z': was added. Any
other fantasies about how it got here, or where it belongs?

My other fantasy is that it was supposed to go here:
241 /*
242 * the following options don't have an equivalent
short option letter,
243 * but are available as '-X long-name'
244 */
245 {"disable-dollar-quoting", no_argument,
&disable_dollar_quoting, 1},
246 {"disable-triggers", no_argument, &disable_triggers,
1},
247 {"use-set-session-authorization", no_argument,
&use_setsessauth, 1},
on line 248.....

I am not sure where it should go, but it seems pretty out of place where it
is.

Sean

You have omitted the "case 0" line following the comment, which is in
fact what it refers to. The -X options return 0 if called in long form,
because then we store a flag rather than returning a distinct value. See
man 3 getopt.

cheers

andrew

Sean Utt wrote:

Thanks! Any reason the comment shouldn't also mention this directly? I.E.:
/*
This covers the long options equivalent to -X xxx.
The -X options return 0 if called in long form, because then we store a flag
rather than returning a distinct value.
See man 3 getopt.
*/

Sean

----- Original Message -----
From: "Andrew Dunstan" <andrew@dunslane.net>
To: "Sean Utt" <sean@strateja.com>
Cc: <pgsql-hackers@postgresql.org>
Sent: Sunday, October 30, 2005 3:05 PM
Subject: Re: [HACKERS] pg_dump option to dump only functions