Обсуждение: Function management in PG

On Tue, Sep 30, 2008 at 2:19 AM, Robins Tharakan <tharakan@gmail.com> wrote:
> Hi,
> While making a complex database back-end, I have at-hand about 200 odd
> functions and frankly 'management of functions' is already getting quite
> tedious. Since the count is certain to rise, I am looking for a good tool to
> do this.
> By management, I guess I am looking at some kind of tagging mechanism, where
> it could keep stored a list of tags for each function (tags that I provide,
> for each new function created) and it be able to provide a comfortable way
> of searching/browsing through the list.

The problem IMO is that you are thinking of the database as a code
repository.  It isn't...you should be thinking of it a compiler.
Strictly speaking what you are asking could be done by simply
searching pg_proc prosrc column for 'tags' you insert into code
comments in the function.  I would not advise doing this.

Keep your 'create function' statements in external files and load them
form there into the database.  Those files can then be checked into a
source control tool, and either organized into a directory tree or
tagged with the in-code documentation.

From there you can build exotic things like scripts that track
function dependencies if you change a core table for example.

merlin

On Tuesday 30 September 2008 02:19:31 Robins Tharakan wrote:
> Hi,
> While making a complex database back-end, I have at-hand about 200 odd
> functions and frankly 'management of functions' is already getting quite
> tedious. Since the count is certain to rise, I am looking for a good tool
> to do this.
>
> By management, I guess I am looking at some kind of tagging mechanism,
> where it could keep stored a list of tags for each function (tags that I
> provide, for each new function created) and it be able to provide a
> comfortable way of searching/browsing through the list.
>
> Originally, I also wished that this could allow me to see whether a given
> function directly (or indirectly) 'can' change the database, but I guess
> the 'Volatile' function type suffices my need there. The problem with that
> is that I still can't see a function's volatility alongside a list of
> functions. Now making a front-end in PHP seems a few days of work, and
> although not impossible, I wondered whether this has already been done.
>

Well, phppgadmin (1) already shows comments for functions in its function
listing, so you could put tagging there (and i think our search feature
searches comments as well). A patch adding volitility and security (definer|
caller) info into the listing screen would probably be accepted :-)

> How do you manage large list of functions (where a hard segregation isn't
> hard and therefore one can't separate functions based on schemas) ?Any
> pointers to a Free or Commercial tool would be of immense help.
>

if you don't due a lot of function overloading, getddl (2) can be used to push
function code into subversion... thats more code management than api
management, but might be helpful. (note, getddl is still pretty rough around
the edges).

(1) http://phppgadmin.sourceforge.net/
(2) https://labs.omniti.com/trac/pgsoltools/wiki/getddl

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL