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:[EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl