PL/pgSQL IDE project

Hello all,

I would like to inform you all that I am currently working on the
implementation of PL/pgSQL packages on both server-side (PostgreSQL 7.1)
and client-side (PgAdmin).
The idea is to add an PL/pgSQL Integrated Development Environment to
pgadmin. Help and suggestions needed. If someone is already working on a
similar project, let me know how I can help. For discussion, please
register on mailto:pgadmin-hackers@greatbridge.org mailing list. Help and
suggestions needed !

First of all, some useful resources:
http://www.oreilly.com/catalog/advoracle/chapter/ch02.html
http://postgresql.rmplc.co.uk/devel-corner/docs/programmer/plpgsql-porting.html

The basic idea behind the project is to store functions and packages in
PgAdmin tables and use drop/create mechanisms to load them in the database.
Here is a first analysis, do not blame in case it is imprecise:

1) Dependencies
The main problem when compiling a set of functions is dependencies :
- transitivity dependencies: if function B relies on function B, and
function A relies on function C, the compilation should be in A, B and C order.
- cross dependencies: if a function A relies on B, B relies on C and C
relies on A, compilation will not work. Warnings should be sent to the user.
According to http://www.oreilly.com/catalog/advoracle/chapter/ch02.html,
this problem exists in Oracle databases (!!!).

To avoid simple dependency problems, we need to work on isolating compiling
mechanisms.

This could be something like :
- functions with no sub calls are compiled first,
- functions with sub calls are compiled secondly, according to an automatic
dependency analysis,
- triggers are compiled at last,
- ultimately, users should be able to define compilation order.

There are maybe more simple mechanisms (???).
Does pg_dump isolate functions in a precise order (???).

2) Isolate Development / Production versions
For every single function, we should isolate the production version
(stable) from the development version (unstable).
This will help debugging and solve dependencies until the project is
'cleanly' compiled and debugged.
This can be done by renaming all functions with the 'unstable_' prefix
during compilation and the use of aliases.

Let's see the example with functionX :
-> functionX is an alias that calls :
stable_functionX (arg1, ...): stable version (production)
unstable_functionX (arg1, ...): unstable version (development)
serial1_functionX (arg1, ...), serial2_functionX (arg1, ...): archived
versions of functionX

Of course, this would be transparent for the developer which will only see
functionX in the IDE.
Switching from unstable_function to stable_function would only require to
recompile the aliases.

3) Serialize package releases
It should be possible to serialize packages and store/reload different
releases.
A logging table will provide a change log (with user names and description
of changes).
I do not intend to work on diffs and don't think it is possible.

4) Server-side logic
Most of the logic should be developed in PL/pgSQL.
On client-side, PgSchema (the new object structure of Pgadmin) will manage
the whole thing.

5) Syntax checking / indenting.
Has anyone heard of open-source objects handling code indenting and syntax
checking ?
I am not going to work on this, help needed.

6) Import / Export of packages
We need a simple mechanism to import/export packages.

7) Master/Slave PL/pgSQL Server
Code should be stored on a master server and distributed to slave servers
through simple mechanisms.
This last logic will be stored in PgSchema as I don't know how to do it
with PostgreSQL itself.
Any possibility to embed it in PostgreSQL (remote call ???).

Looking forward to hearing from you,
Greetings from Jean-Michel POURE, Paris