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
