PL/pgSQL IDE project - Mailing list pgsql-hackers

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