Casey Allen Shobe :: Documents :: PostgreSQL Wishlist

The following things are just stuff that I've encountered a need or desire for. The official PostgreSQL TODO list may contain some of the same things or not...this is just a personal list.

The following are functional things I really miss PG having:

• Job Scheduling - Scheduled Tasks / Cron-like functionality within the database (MySQL, Oracle).
• Secure Application Roles - Roles that depend on a function returning success, and roles that can be enabled/disabled by the user (Oracle).
• Autonomous Transactions - for committing stuff to a database before a function is complete (Oracle).

The following I can get by fine without, but would be nice to have:

• Materialized Views - can be done in pg with triggers, but is tedius repetitive work to set them up.
• Recycle Bin/Flashback functionality, for recovering dropped objects (Oracle, Oracle 2).
• Access/Modify/Create Timestamps on database objects - selected by configuration options. Modify times would be when the object was last modified with a DDL command such as ALTER or CREATE OR REPLACE. Should be possible to select which sorts of objects which sorts of times are desired for.
• Object Searching Functions - I have a useful SQL function that finds content within other functions, "select n.nspname || '.' || s.proname from pg_proc s join pg_namespace n on s.pronamespace = n.oid where s.prosrc like '%' || $1 || '%' order by n.nspname, s.proname", which has proven very useful. But it only works for functions and is not powerful enough as I would sometimes like to specify multiple search patterns and/or add exclusion filters.
• \ef <function_name> in psql should fire up $EDITOR to modify a function, like vipw does for /etc/passwd. Being added in PostgreSQL 8.4
• DDL Automated Extensions - every time I create a table with an id column, I create a simple SQL function to return the ID by name or other identifier, i.e. for email.addresses I have email.address_id (<email_address>). I would like to be able to set PG to automate this somehow...i.e. create a rule that says "whenever a table is created that contains a primary key named 'id', and a unique index on a column named 'name', create this here sql function, using the table name as part of it. Not sure this is really feasible... Another example - for every schema I create, I create a nologin role called <schemaname>_access. Seems that should be easy to automate.
• COMMENT ON ROLE support. Added in PostgreSQL 8.2 along with database and tablespace comments.
• ALTER TABLE should support a way to rename constraints (current workaround begin; alter table drop constraint ...; alter table add constraint ...; end;)
• Upon table rename, indexes and constraints in the table, and foreign key constraints in other tables pointing at it need manually updated to match the change. It would probably be possible to make this automatic.
• ALTER VIEW ... RENAME COLUMN to change the view's column name without recreating the whole view. Currently ALTER TABLE ... RENAME COLUMN works when used on a view, but this is not intuitive nor clear to most users.
• It would be handy for documentation update purposes, and in certain other cases, to have the ability to create triggers for COMMENT and RENAME actions.