Waiting for PostgreSQL 9 stable version

I just read PostgreSQL 9.0 Beta 2 and it's feature on this. Many new feature there, and drive me to use it in future. Things that I am interested in:

Backend section:
1. Column level permissions: allows specification of granular column-level permissions in addition to table level grants
2. GRANT/REVOKE ON ALL TABLES/SEQUENCES/FUNCTIONS: simplifies permission management by allowing mass changes of GRANTS

Functions and triggers section:
1. Column level triggers: adds support for SQL standard column level trigger support, by allowing triggers to only fire if specific columns are mentioned in the UPDATEs SET list
2. WHEN clause for CREATE TRIGGER: adds support for a boolean expression to be checked if a trigger should be fired or not
3. RETURNS TABLE: SQL standard RETURNS TABLE clause for CREATE FUNCTION.

Cryptographic on FreeBSD

I have two Postgresql installed one on Windows (at home) and FreeBSD (at some place out there). I use pgcrypto for encrypting sensitive data. On Windows I have installed the library by executing C:\sistemku\PostgreSQL\9.0\share\contrib\pgcrypto.sql file.

Now it's time for me to send my work to remote server.
I am making a backup secret.db by using pg_dump, then transfer the backup file onto remote server using FTP command. With PuTTY via modem connection, I restored the database. Make some test of web site contents, they looks okay except for crytographic functions. Yeah...the FreeBSD server has no pgcrypto installed.

#find / -name pgcrypto.sql (the file does not exist).


1st attempt: Execute pgcrypto.sql, as you might suspect.., it failed. I have FTPed the file before.
2nd attempt: Type this command using psql:

CREATE OR REPLACE FUNCTION digest(text, text)
RETURNS bytea AS '$libdir/pgcrypto', 'pg_digest'
LANGUAGE C IMMUTABLE STRICT;

Again an error message raised "libdir not found".

3rd attempt: Use port to install pgcrypto with the following steps:
1. Install pgcrypto module
#cd /usr/ports/database/postgresql-contrib
#make config
#make install clean

I could found /usr/local/lib/postgresql/pgcrypto.so and /usr/local/share/postgresql/contrib/pgcrypto.sql.


2. To access library functions, I load stored procedures onto my secret database.
# psql -U binsar -d secredb -f /usr/local/share/postgresql/contrib/pgcrypto.sql


3. Check if cyptographic function loaded.

secretdb=# \df digest
List of functions
Schema Name Type Argument
-------+-------+-------+------------
public digest bytea bytea, text
public digest bytea text, text


4. It works, and get my coffee.