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

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(at)postgresql(dot)org