Re: PostgreSQL Top 10 Wishlist

On Jan 13, 2006, at 13:51 , rlee0001 wrote:

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

There *is* a lot of documentation, but it's also quite thorough. You
might want to take some time and look through it.

http://www.postgresql.org/docs/current/interactive/index.html

Selected responses below.

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

You can definitely cast boolean to integer:

test=# select version();

version
------------------------------------------------------------------------
----------------------------------------------------------------------
PostgreSQL 8.1.0 on powerpc-apple-darwin8.3.0, compiled by GCC
powerpc-apple-darwin8-gcc-4.0.0 (GCC) 4.0.0 (Apple Computer, Inc.
build 5026)
(1 row)

test=# select false::boolean::integer;
int4
------
     0
(1 row)

test=# select true::boolean::integer;
int4
------
     1
(1 row)

And you can create your own casts to text if you'd like. See the docs:

http://www.postgresql.org/docs/current/interactive/sql-createcast.html

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

You can dump the schema of a database using pg_dump --schema-only.
Does this do what you want?

http://www.postgresql.org/docs/current/interactive/app-pgdump.html

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

Andrew Dunstan has developed EnumKit to allow you to have enumerated
data types in PostgreSQL. Hopefully this can help you.

http://www.oreillynet.com/pub/a/databases/2006/01/06/enumerated-
fields-in-postgresql.html

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

While PL/pgsql won't let you do this, you can probably do some things
with pl/perlu or some of the other untrusted languages. Also, dbi-
link may help you as well.

http://pgfoundry.org/projects/dbi-link/

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

Have you looked at composite types?
http://www.postgresql.org/docs/current/interactive/sql-createtype.html


>
> 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 can update a view if you create rules to do so:

http://www.postgresql.org/docs/current/interactive/rules.html

> This is really borrowing
> heavily from object oriented concepts (class properties in VB are
> defined like this).

While there are some similarities between classes and tables, and
objects and rows, they're not the same thing. Some of the things you
describe can be done using user-defined functions, while others are
probably better done in your application. You can also create your
own datatypes that give you all the functionality you want:
PostgreSQL is *very* extensible.

Hope this helps a bit.

Michael Glaesemann
grzm myrealbox com