ERROR: language "plpgsql" does not exist

A freshly installed PostgreSQL 8.3 on OSX (Snow Leopard) via MacPorts would not accept the plpgsql language, this can be fixed easily:

-- Create a function handler:
CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS '$libdir/plpgsql' LANGUAGE C;

-- Create the language 'plpgsql'
CREATE TRUSTED LANGUAGE plpgsql
HANDLER "plpgsql_call_handler";

-- and I don't know if is a good thing to :
GRANT USAGE ON LANGUAGE plpgsql TO public;

Start/Stop/Restart PostgreSQL 8.3

Installed PostgreSQL 8.3 with MacPorts (Snow Leopard). This script wirl make it easy to start/stop/reload your local PostgreSQL server:

#!/bin/bash

echo "You need sudo rights to perform this action ..."

plist="/Library/LaunchDaemons/org.macports.postgresql83-server.plist"
launchctl=$(which launchctl) # if needed, set this to an absolute path

if [[ -z "$1" ]]; then
 echo "Usage $(basename $0) start|stop|reload"
 exit 1
fi

mode="load"

if [[ "$1" == "start" ]]; then mode="load";
elif [[ "$1" == "stop" ]]; then mode="unload"; 
elif [[ "$1" == "reload" ]]; then mode="reload"; 
else
 echo "Unknown mode '$1'";
 exit 2;
fi

if [[ "$mode" == "reload" ]]; then
 exit $(sudo $launchctl unload -w "$plist" && \
        sudo $launchctl load -w "$plist")
fi

sudo $launchctl "$mode" -w "$plist"

PostgreSQL 8.3 on OSX with MacPorts

Installing PostgreSQL on OSX Snow Leopard is pretty sinple, just install MacPorts and the issue the command:

$ sudo port install postgresql83 +universal

Start the server with:

$ sudo launchctl load -w /Library/LaunchDaemons/org.macports.postgresql83-server.plist

Stop the Server with:

sudo launchctl unload -w /Library/LaunchDaemons/org.macports.postgresql83-server.plist

Use PgAdmin III for getting started and administering your databases.

PostgreSQL 8.3 Table with last insterted and creation date

This is a template for PostgreSQL 8.3 which includes

• «id» Primary Key as AUTO_INCREMENT
• «ts_insert» date/time of creation of this record
• «ts_lastmod» date/time of last modification of this record

/*
Template with auto increment id, last modified and insert date

NOTE: replace
 «$tbl» with your table name and
 «$owner» with your owning user

$Id$
*/

-- Function: update_lastmodified_column()

-- DROP FUNCTION update_lastmodified_column();

CREATE OR REPLACE FUNCTION update_lastmodified_column()
 RETURNS trigger AS
$BODY$
 BEGIN
   NEW.ts_lastmod = NOW();
   RETURN NEW;
 END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE
 COST 100;
ALTER FUNCTION update_lastmodified_column() OWNER TO $owner;


-- Table: $tbl

-- DROP TABLE $tbl;

CREATE TABLE $tbl
(
 id serial NOT NULL,
 ts_insert timestamp with time zone DEFAULT now(),
 ts_lastmod timestamp with time zone DEFAULT now(),
 CONSTRAINT $tbl_id PRIMARY KEY (id)
)
WITH (
 OIDS=FALSE
);
ALTER TABLE $tbl OWNER TO $owner;

-- Trigger: update_lastmodified_modtime on $tbl

-- DROP TRIGGER update_lastmodified_modtime ON $tbl;

CREATE TRIGGER update_lastmodified_modtime
 BEFORE UPDATE
 ON $tbl
 FOR EACH ROW
 EXECUTE PROCEDURE update_lastmodified_column();