Re: PostgreSQL Top 10 Wishlist

Martijn van Oosterhout wrote:
> 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?

TG_STATEMENT would be the original statement entered by the user which
caused the trigger to execute exactly as it was entered.
TG_EFFECTIVE_STATEMENT would be the statement re-written for that row
only (the row's primary key would be identified in the WHERE clause).

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

Auditing with row granularity. So that managers can see what queries
are being run against the database and impliment per-row rollback
capabilities to the system.

> > 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);

This is a privilaged operation. I don't own the type pg_catalog.boolean
on our production server and therefore cannot create a cast for 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.
>
> 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.

Thats basically what I've been doing but using the EMS PostgreSQL front
end.

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

Thats true enough.

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

I'll look into it. This is a hot topic here among even the managers so
there might be a chance they'd impliment it. Thanks!

> > 8.
> > The ability to use procedural-language extensions everywhere, not just
> > in functions.
>
> Like where? Give an example.

// PHP
rows = pg_query('IF ... THEN ... ENDIF;');

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

Really I was dreaming up some way to organize the data in large tables.
The only reason I mention it is that we have a table with 90 fields and
looking at it is an eye-soar. :o)

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

Actually I might try to have a go at it just for fun at home. Here at
work I just don't have the ability to create types (AFAIK).

> Have a nice day,
> --
> Martijn van Oosterhout <kleptog(at)svana(dot)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.