Re: PostgreSQL Top 10 Wishlist

On Thu, Jan 12, 2006 at 08:51:44PM -0800, rlee0001 wrote:
> 1.
> Two new special variables in triggers functions (TG_STATEMENT and
> TG_EFFECTIVE_STATEMENT) which returns the statement that triggered the
> trigger.

Which would that be? The statement that directly invoked the trigger,
or the one the user typed, or would you want a list of all of them?

> 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.

Why, when NEW has all the info you need in a much easier to use format?
Seems pretty pointless to me...

> 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.

So make them? It's not like it's hard:

CREATE CAST (boolean AS varchar) USING FUNCTION bool_to_varchar(bool);

> 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.

psql gives you that. If you give -E it'll even show you the queries it
uses to make the info. Also, the information_schema should have most
stuff you want.

> 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!

Someone actually mosted a patch that did this. Funnily enough, it'd
probably be implemented by creating seperate tables for each ENUM to do
the lookup. It's just suger-coating really...

> 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.

dblink does it for postgres DBs, there are similar modules for
connections to other databases.

> 8.
> The ability to use procedural-language extensions everywhere, not just
> in functions.

Like where? Give an example.

> 9.
> The ability to nest fields within fields. For example:
>   PERSON
>      NAME
>          LAST
>          FIRST
>      PHONE

You can sort of do this, using rowtypes. Havn't nested more than one
level though. Not sure why you'd want this though. A database stores
data, presentation is the application's job.

> 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.

You are wrong, you can make updatable views.

> 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.

So, create a type that does that. PostgreSQL is extensible. It's got
data types for ISBNs, Internet addresses and even an XML document type.
Compared to that a simple phone number field would be trivial.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.