Re: One more option for pg_dump...

Hi all,
On the 6th of february, there's been a thread about adding new options to
pg_dump, but it is now too late for me to add comments to this thread, since
all that was said wouldn't be readable at this time, so I add an new thread
here.
I haven't found any option to dump any user-defined function stored in a
database, unless doing a pg_dump -D -s database, but so far one would get the
definitions of the tables, the permissions, the triggers, and so on, so when
you have a big schema, it is not much user friendly to do a full dump to
change one or two lines of code in a function.
Could there be an option to pg_dump (let's say --function [func_name]) to be
abble to dump the complete source code of a function in a separate file, or
on the terminal ?
I've found that when in psql, one can type \df+ func_name to have many
informations about the so named func_name, but it is not well readeable.
Do you think it could be a good thing ?
Are there workarounds to have the same comportement that I'm not aware of ?
Thanks for your ideas about this.

On Mon, Feb 25, 2008 at 10:48 AM, David BOURIAUD
<david(dot)bouriaud(at)ac-rouen(dot)fr> wrote:
> Could there be an option to pg_dump (let's say --function [func_name]) to be
> abble to dump the complete source code of a function in a separate file, or
> on the terminal ?

It's a TODO item. Just not to functions and so others (operators,
casts,...) objects as well.

I'm coding a fully functional prototype that solves these features.
Just now I'm going think in a way to dump overloaded functions that
seems me one more complicated issue.

> Do you think it could be a good thing ?

Yep! Hence it's a todo item :-)

> Are there workarounds to have the same comportement that I'm not aware of ?

Alot of lines sed+awk+pg_dump scripts ..

-Leo
--
Leonardo Cezar et all
http://www.dextra.com.br/postgres
http://www.postgresql.org.br

Le lundi 25 février 2008, Leonardo Cezar a écrit :

Hi Leonardo,
Thanks for your quick answer, I didn't know it was a TODO item, and that
somepeople were working on it... Keep going, then, cause I'm really waiting
for these features !

> On Mon, Feb 25, 2008 at 10:48 AM, David BOURIAUD
>
> <david(dot)bouriaud(at)ac-rouen(dot)fr> wrote:
> > Could there be an option to pg_dump (let's say --function [func_name])
> > to be abble to dump the complete source code of a function in a separate
> > file, or on the terminal ?
>
> It's a TODO item. Just not to functions and so others (operators,
> casts,...) objects as well.
>
> I'm coding a fully functional prototype that solves these features.
> Just now I'm going think in a way to dump overloaded functions that
> seems me one more complicated issue.
>
> > Do you think it could be a good thing ?
>
> Yep! Hence it's a todo item :-)
>
> > Are there workarounds to have the same comportement that I'm not aware
> > of ?
>
> Alot of lines sed+awk+pg_dump scripts ..

Nay, I use vim with two buffers, search for the code I want and copy-paste,
but reconn that it's not very user friendly !
Thanks again, I'll try to wait for the improvements in the next version of
postgres !
>
> -Leo

On Mon, Feb 25, 2008 at 12:33 PM, David BOURIAUD
<david(dot)bouriaud(at)ac-rouen(dot)fr> wrote:
> Le lundi 25 février 2008, Leonardo Cezar a écrit :
>
> Hi Leonardo,
> Thanks for your quick answer, I didn't know it was a TODO item, and that
> somepeople were working on it... Keep going, then, cause I'm really waiting
> for these features !

As I said before, I'm writing a *proposal* (proto) to patch which I
should publish here at the next days. So as It would be the first
dump's patch I'd like to deal among other things: what's a better
syntax and so on.

-Leo
--
Leonardo Cezar et all
http://www.dextra.com.br/postgres
http://www.postgresql.org.br

On Tue, Feb 26, 2008 at 12:48 AM, David BOURIAUD
<david(dot)bouriaud(at)ac-rouen(dot)fr> wrote:
> I haven't found any option to dump any user-defined function stored in a
> database, unless doing a pg_dump -D -s database, but so far one would get the
> definitions of the tables, the permissions, the triggers, and so on, so when
> you have a big schema, it is not much user friendly to do a full dump to
> change one or two lines of code in a function.

Currently the intelligence about how to "dump" a function (that is,
take a pg_proc row and come up with a CREATE FUNCTION statement) is
coded directly into pg_dump.

Wouldn't it be more helpful if this was provided as a function by the
backend? That way you wouldn't have to mess with pg_dump at all, if
you were in the OP's situation and just wanted to grab the definition
for one (or several) functions.

You could just pull up a psql session and do a "select
pg_func_def(regproc);" and there you go, one fully formed CREATE
FUNCTION statement.

It leaves the question of what to do about COMMENTs up in the air,
since you would need to dump it as two separate statements, but I
don't think that is as important as being able to pull arbitrary
object defs from the database without resorting to kludgery.

Regards,
BJ

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 27 Feb 2008 13:05:44 +1100
"Brendan Jurd" <direvus(at)gmail(dot)com> wrote:

>
> You could just pull up a psql session and do a "select
> pg_func_def(regproc);" and there you go, one fully formed CREATE
> FUNCTION statement.

\df+ function(type)

Joshua D. Drake

- --
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHxNkzATb/zqfZUUQRAnI0AJ49PX6qADSDgqvHQ9boQfeVZhw+TQCfVL1q
5s0hlj44H0XhM3TYoCWX3dE=
=P8Ds
-----END PGP SIGNATURE-----

On Wed, Feb 27, 2008 at 2:29 PM, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
> > You could just pull up a psql session and do a "select
> > pg_func_def(regproc);" and there you go, one fully formed CREATE
> > FUNCTION statement.
>
> \df+ function(type)
>

Sure, if your idea of a good time is looking at the output of \df+,
and then querying pg_proc anyway to find out whether the function is
strict or not, and then writing out a CREATE FUNCTION by hand. \df+
is great for getting a quick idea of what functions are available and
what they do. For producing executable SQL to re-create those
functions, it's a mess.