Thread: Extracting object source code from database to store in CVS...

Hello,

I'm working on a project which uses postgresql (7.4.x), and a lot of
the project code is in functions, views, etc. in a postgresql
database.  I would like to create an automated process for extracting
all this code to individual text files (which can be managed through a
version control system), and for putting the code in the text files
back in the database.

To begin with, has this sort of thing been done before?  I don't want
to re-invent the wheel (unless it's a wheel with a proprietary
licence).

More specifically, I've been looking for a way to persuade postgresql
to output the create script for a single object - without much
success.  It seems that pg_dump can output a dump of the entire
database schema or a dump of a single table, but not of a different
kind of object like a function or view.  So at the moment it looks
like I'll have to parse the output of the psql "\d" commands into
create scripts by myself.

Does anyone here know of a (linux) command-line utility, or a function
which can be added to psql, which produces create scripts for single
objects?  I believe that mysql has a built-in command that does it;
that's the functionality I'm looking for.

Regards
Adrianna
--
Ph'nglui mglw'nafh Cthulhu R'lyeh wgah'nagl fhtagn!
--Registered Linux User #334504--

You can look at the system catalogs.  In particular, look at:

http://www.postgresql.org/docs/current/static/catalogs.html
http://www.postgresql.org/docs/current/static/catalog-pg-proc.html

You can do something like

select proname,prosrc from pg_proc;

as an example.  You could use one of the procedure languages like
plperl to grab all the current function definitions, dump them to text
files with respective names (some notice will have to be paid to
overloaded functions, I suppose), and when necessary, read in the
file(s) of new/edited functions and executing the sql to drop/create or
recreate them.

Hope this helps
Sean



On Mar 23, 2005, at 7:10 AM, Adrianna Pinska wrote:

> Hello,
>
> I'm working on a project which uses postgresql (7.4.x), and a lot of
> the project code is in functions, views, etc. in a postgresql
> database.  I would like to create an automated process for extracting
> all this code to individual text files (which can be managed through a
> version control system), and for putting the code in the text files
> back in the database.
>
> To begin with, has this sort of thing been done before?  I don't want
> to re-invent the wheel (unless it's a wheel with a proprietary
> licence).
>
> More specifically, I've been looking for a way to persuade postgresql
> to output the create script for a single object - without much
> success.  It seems that pg_dump can output a dump of the entire
> database schema or a dump of a single table, but not of a different
> kind of object like a function or view.  So at the moment it looks
> like I'll have to parse the output of the psql "\d" commands into
> create scripts by myself.
>
> Does anyone here know of a (linux) command-line utility, or a function
> which can be added to psql, which produces create scripts for single
> objects?  I believe that mysql has a built-in command that does it;
> that's the functionality I'm looking for.
>
> Regards
> Adrianna
> --
> Ph'nglui mglw'nafh Cthulhu R'lyeh wgah'nagl fhtagn!
> --Registered Linux User #334504--
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org

On Wed, 23 Mar 2005 07:32:08 -0500, Sean Davis <sdavis2@mail.nih.gov> wrote:
> You can look at the system catalogs.  In particular, look at:
>
> http://www.postgresql.org/docs/current/static/catalogs.html
> http://www.postgresql.org/docs/current/static/catalog-pg-proc.html

Thanks - the info you get from these is the same as what is produced
by the built-in psql describe functions, but it's in a more useful
format.  This will make creating the scripts a little simpler.

Apologies for the duplicate mail, by the way.

Adrianna
--
Ph'nglui mglw'nafh Cthulhu R'lyeh wgah'nagl fhtagn!
--Registered Linux User #334504--

On Wed, Mar 23, 2005 at 14:10:30 +0200,
  Adrianna Pinska <adrianna.pinska@gmail.com> wrote:
>
> More specifically, I've been looking for a way to persuade postgresql
> to output the create script for a single object - without much
> success.  It seems that pg_dump can output a dump of the entire
> database schema or a dump of a single table, but not of a different
> kind of object like a function or view.  So at the moment it looks
> like I'll have to parse the output of the psql "\d" commands into
> create scripts by myself.

Note that you can use the -E option to see what queries psql uses to
create its output. This might help you write the queries you need to
dump function bodies.

Adrianna Pinska <adrianna.pinska@gmail.com> writes:
> More specifically, I've been looking for a way to persuade postgresql
> to output the create script for a single object - without much
> success.  It seems that pg_dump can output a dump of the entire
> database schema or a dump of a single table, but not of a different
> kind of object like a function or view.

The fact that pg_dump has restrictive switches for selecting tables but
not other kinds of objects isn't fundamental; it's just that no one has
gotten around to it.  Perhaps your best approach in the long term is to
implement such switches.  If you can do that and get it accepted into
the code base, then you won't have to worry about keeping your code
up-to-date with future system catalog changes.

Based on past history, I'd say that trying to maintain your own pg_dump
subset is a losing proposition.  We whack the catalogs around a lot ...

            regards, tom lane