I've been using PostgreSQL 8.1 with EMS PostgreSQL Manager and PHP for
about a month now and here are the top 10 features I'd like to see.
Keep in mind that I'm a novice so we might have some of this and I just
can't find it in the docs.
1.
Two new special variables in triggers functions (TG_STATEMENT and
TG_EFFECTIVE_STATEMENT) which returns the statement that triggered the
trigger. This should be able to be used in row- or statement-level
triggers. For row level triggers I would like TG_EFFECTIVE_STATEMENT to
return the valid statement that operates on that row only. For example
the actual statement:
UPDATE inventory SET status = 0 WHERE status = 1;
...would be rewritten as:
UPDATE inventory SET status = 0 WHERE id = 2335;
...when accessed from within a row-level trigger for the row who's
primary key (id) equals 2335.
2.
The ability to typecast from boolean to other datatypes. For example:
false::varchar
...would return varchar 'false' while:
false::integer
...would return integer 0. Currently there seems to be no way to
typecast from boolean (please correct me if I'm wrong). This is quite
disappointing since you can typecast into boolean.
3.
The ability to disable rules, triggers, views, functions, languages and
the like without dropping them. Maybe we have this and EMS just doesn't
impliment it?
4.
The ability to view the DDL for objects. Logically I know that this HAS
to be possible already but I can't figure it out and a search of the
documentation doesn't mention it. You can do this in EMS PostgreSQL
Manager but I can't figure out how to query it on my own.
5.
The SET and ENUM data types. I know MySQL is cheap and evil but even it
has them. Both are really just Integers attached to some Metadata. You
have no idea how many descriptor tables I have for simple enumerations.
Some have less than 10 items in them!
6.
Cross database queries. I'd like to be able to query a MS SQL Server
database from within PL/PGSQL. Or at least other databases on the same
server. Granted it might not be possible to JOIN, UNION or Subquery
against them but I'd at least like to be able to perform a query and
work with the results. We currently have to feed a postgresql database
daily snapshots the live Microsoft SMS network data using a DTS
package. Being able to access the Live data (especially if we could
join against it) would be awesome.
7.
An XML field type and associated XPath/DOM functions. Other exotic
field types like Image might be nice for some people as well. But XML
would be awesome.
8.
The ability to use procedural-language extensions everywhere, not just
in functions.
9.
The ability to nest fields within fields. For example:
PERSON
NAME
LAST
FIRST
PHONE
10.
Or an alternative to views where tables can be defined with virtual
fields which point to functions. So for example I can say:
SELECT balance, name FROM customers WHERE balance < 0;
...where balance actually performs a behind the scenes JOIN against a
transactions table and totals the customers credits and debits. I
realize views can do this but for adding a single dynamic field they
are cumbersome and correct me if I'm wrong but I don't think you can
UPDATE against a view. Such fields can have two functions: GET and SET.
SET executes when the field is updated. If the SET procedure is not
specified updating the field could throw an exception (e.g. read only).
If SET is specfied but doesn't do anything the update would be ignored.
This effectively impliments triggers with column granularity. DELETE
and INSERT clauses could be added as well. This is really borrowing
heavily from object oriented concepts (class properties in VB are
defined like this). Now suppose we take this a step farther down the
road of rows being objects and give them private and public fields.
Public fields can be queried against from outside the table's own
virtual field functions while private fields are hidden. Public fields
can validate and normalize data before storing that data internally for
example. For example:
In: 123 456-7890
Out: (123) 456-7890
Stored As:
PHONE = (Virtual Function, with Regexp input parser)
AREA_CODE = 123
PREFIX = 456
SUFFIX = 7890
It would be interesting. Combine with item 9 above and you can make
"name" output in a structured format like "Last, First". Vb.Net's IDE
does this in the properties list for nested properties.
Just some stupid ideas.
-Robert
