Re: Wishlist? - Mailing list pgsql-general

Jim C. Nasby wrote:
> http://www.postgresql.org/developer/roadmap
>
> Note especially the part about developers scratching their own itch. You
> can request all you want, but unless a number of developers agree it's a
> good idea it probably won't make it to the TODO. And stuff can get
> pulled from the TODO without actually being done.
>
> You'll have better luck getting something added if you're willing to
> commit to developing it (or pay someone else to).
>
> Having said all that, if you want to just request stuff (that's not
> already on the TODO), you can just post it here and it might get on the
> TODO if there's enough interest. If you want to discuss specifics,
> -hackers is probably a better place.

Thanks a lot, I've read the TODO and have now an idea of what is the
current trend or plans for future releases.

Regarding my own needs, I'd be happy to "scratch my own itch", but
perhaps I should find some consensus before I engage myself in the task
of developing. And I would also need a little guidance in regards to how
to submit changes and all that.

I am a long time user of MS SQL Server (6.5, 7, and 2000) and recently
started employing PostgreSQL 8 (Windows version) for some projects, with
aims of replacing the SQL Server entirely soon. But the migration was
everything but smooth, even though SQL Server, in comparison with other
RDBMSs like Oracle, is fairly simple. So I would like to know how
populate would be the following additions to make PostgreSQL more
feature-compatible with SQLServer, for the future and current converts
like myself:

* Stored procedures: Although similar to functions, SP always return an
execution status rather than a result, but provide the caller with the
equivalent of running several sql instructions, as if running a script.
Unlike functions, their results cannot be utilised by most other
statements, with the exception of INSERT [...] EXECUTE [...]. Their main
attractive is to simplify the detachment between interface and
implementation. Perhaps this could be achieved extending some features
of functions, perhaps enabling a combination of SETOF result (which
needs an externally defined result type) and OUT parameters (which is
flexible but restricted to a single row), or handled similarly as the
current possibility to return cursors.

* Scripting language: A very powerful feature in SQLServer is its
support of variables and control structures embedded in the SQL
language, thus allowing running complex scripts without the need to pack
them in a function and then running the function. This is especially
limiting in PG since it doesn't support the creation of temporary
functions. In MSSSQL, variables are prefixed with AT (@) symbols, must
be declared before they're used, live only within the prepared statement
and can be set using SET, SELECT and UPDATE. Also, the conditional
statements are only IF and WHILE, they support subqueries and function
calls in their expressions, and group statements using BEGIN and END. I
find prefixing variables thus is a lot less confusing and error prone
than the parameter alias and variables used in PL/pgSQL. For the
implementation on PG, I believe that perhaps it would be nice to use
curly brackets instead of BEGIN and END.

* Inter-database access: Although PG greatly benefits from the use of
schemes, many times different applications are required to use separate
databases but still sharing some resources. The only current facility
that I'm aware of for this purpose in PG is through pl-perl functions,
but this prevents reusing current credentials and greatly reduce
flexibility.

I'd like also to propose the following features:

* Allow FETCH command to be used with CREATE TABLE tab AS qry (in place
of qry)

* Allow to optionally prevent overloading in functions, thus allowing
the CREATE OR REPLACE command replace the function despite having
different parameters

* Set the SERIAL automatically generated sequences as system objects,
thus allowing them to be hidden in the admin GUIs

* Allow a function's result type to be defined in the function itself,
similarly to the way OUT parameters are constructed. If a type for it
must be created for this purpose, set it as a system object and handle
its recreation automatically upon CREATE OR REPLACE FUNCTION to avoid
the current catch-22 dependency issues. Or perhaps let function
returning a SETOF RECORD to have an implicit (undefined) result type,
thus avoiding having to specify the type dynamically in the caller
statement SELECT func(...) AS (...)

* Create a ROWSET variable type in plpgsql that would function like a
temporary but in-memory table. Support for it would have to be added for
plpgsql's SELECT, UPDATE and DELETE statements.

* Make temporary schemes visible (and accessible) only to its current
owner/user, and create a tool to clear currently unused temporary
schemes (for instance during backups, or when duplicating a database)

* Allow inserting new columns in arbitrary positions with ALTER TABLE
tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like mysql does.

I hope a few of you will be interested in my proposals, so they get into
the TODO list. Once there, I'd be happy to contribute with some
development myself (either for these or other TODO items). I'm not a
proficient hacker with lots of time available, but I believe I'm a
fairly good programmer and I'm a Gentoo user so I have everything needed
to compile and build. But I believe I'll require a few months of
studying the code alone before I dare making any additions. So any hints
of how to become a PG developer to submit changes, and where to start
(or what documentation to read first) to have a fast head-start will be
highly appreciated.

Cheers,

Ezequiel Tolnay