Failed to create a function

Hi guys,

OK, I'm able to get postmaster running, create and drop a db, create
and drop tables.

Now I'm trying to create a function (for a trigger) which is a port
from an oracle stored procedure, and I don't understand why it fails
since the syntax appears to be correct from the book examples.

These are the three dollops of sql that I've been pasting in...

create table WDRole (
WDRoleID integer,
name varchar(16),
primary key (WDRoleID)
);

...which goes in ok, then...

create table WDVolume (
WDVolumeID integer,
Mountpoint varchar(255),
Name varchar(255),
Readable char(1),
Writeable char(1),
DiskAllocated integer,
DiskUsed integer,
DiskAvailPC decimal(5,2),
TotalFiles integer,
LastFileSeq integer,
primary key (WDVolumeID)
);

...which goes in ok, then...

CREATE FUNCTION trigger_WDVolume () RETURNS opaque AS '
DECLARE
-- set the default disk space
disk_avail CONSTANT integer := 100;

BEGIN
IF OLD.DiskUsed is null THEN
NEW.DiskAvailPC := disk_avail;
ELSE
NEW.DiskAvailPC := disk_avail - (disk_avail * OLD.DiskUsed) /
OLD.DiskAllocated;
ENDIF;

RETURN NEW;
END
' LANGUAGE 'plpgsql';

...and this is the tty session which turns to poo...

Last login: Tue Nov 25 12:58:30 on ttyp1
Welcome to Darwin!
[MacGregor:~] roy% su postgres
Password:
[MacGregor:/Users/roy] postgres% pg_ctl -D /usr/local/pgsql/data status
pg_ctl: postmaster is running (pid: 456)
Command line was:
/usr/local/bin/postmaster
[MacGregor:/Users/roy] postgres% psql template1
Welcome to psql 7.3.3, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

template1=# create database webdav;
CREATE DATABASE
template1-# \q
[MacGregor:/Users/roy] postgres% psql webdav
Welcome to psql 7.3.3, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

webdav=# create table WDRole (
webdav(# WDRoleID integer,
webdav(# name varchar(16),
webdav(# primary key (WDRoleID)
webdav(# );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'wdrole_pkey' for table 'wdrole'
CREATE TABLE
webdav=# create table WDVolume (
webdav(# WDVolumeID integer,
webdav(# Mountpoint varchar(255),
webdav(# Name varchar(255),
webdav(# Readable char(1),
webdav(# Writeable char(1),
webdav(# DiskAllocated integer,
webdav(# DiskUsed integer,
webdav(# DiskAvailPC decimal(5,2),
webdav(# TotalFiles integer,
webdav(# LastFileSeq integer,
webdav(# primary key (WDVolumeID)
webdav(# );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'wdvolume_pkey' for table 'wdvolume'
CREATE TABLE
webdav=# CREATE FUNCTION trigger_WDVolume () RETURNS opaque AS '
webdav'# DECLARE
webdav'#
ABORT BEGIN COMMENT CREATE DROP GRANT LOAD
NOTIFY REVOKE SET UNLISTEN
ALTER CLOSE COMMIT DECLARE EXPLAIN INSERT LOCK
REINDEX ROLLBACK SHOW UPDATE
ANALYZE CLUSTER COPY DELETE FETCH LISTEN MOVE
RESET SELECT TRUNCATE VACUUM
webdav'# -- set the default disk space
webdav'#
ABORT BEGIN COMMENT CREATE DROP GRANT LOAD
NOTIFY REVOKE SET UNLISTEN
ALTER CLOSE COMMIT DECLARE EXPLAIN INSERT LOCK
REINDEX ROLLBACK SHOW UPDATE
ANALYZE CLUSTER COPY DELETE FETCH LISTEN MOVE
RESET SELECT TRUNCATE VACUUM
webdav'# disk_avail CONSTANT integer := 100;
webdav'#
ABORT BEGIN COMMENT CREATE DROP GRANT LOAD
NOTIFY REVOKE SET UNLISTEN
ALTER CLOSE COMMIT DECLARE EXPLAIN INSERT LOCK
REINDEX ROLLBACK SHOW UPDATE
ANALYZE CLUSTER COPY DELETE FETCH LISTEN MOVE
RESET SELECT TRUNCATE VACUUM
webdav'#
webdav'# BEGIN
webdav'#
ABORT BEGIN COMMENT CREATE DROP GRANT LOAD
NOTIFY REVOKE SET UNLISTEN
ALTER CLOSE COMMIT DECLARE EXPLAIN INSERT LOCK
REINDEX ROLLBACK SHOW UPDATE
ANALYZE CLUSTER COPY DELETE FETCH LISTEN MOVE
RESET SELECT TRUNCATE VACUUM
webdav'# IF OLD.DiskUsed is null THEN
webdav'#
ABORT BEGIN COMMENT CREATE DROP GRANT LOAD
NOTIFY REVOKE SET UNLISTEN
ALTER CLOSE COMMIT DECLARE EXPLAIN INSERT LOCK
REINDEX ROLLBACK SHOW UPDATE
ANALYZE CLUSTER COPY DELETE FETCH LISTEN MOVE
RESET SELECT TRUNCATE VACUUM
webdav'#
ABORT BEGIN COMMENT CREATE DROP GRANT LOAD
NOTIFY REVOKE SET UNLISTEN
ALTER CLOSE COMMIT DECLARE EXPLAIN INSERT LOCK
REINDEX ROLLBACK SHOW UPDATE
ANALYZE CLUSTER COPY DELETE FETCH LISTEN MOVE
RESET SELECT TRUNCATE VACUUM
webdav'#
ABORT BEGIN COMMENT CREATE DROP GRANT LOAD
NOTIFY REVOKE SET UNLISTEN
ALTER CLOSE COMMIT DECLARE EXPLAIN INSERT LOCK
REINDEX ROLLBACK SHOW UPDATE
ANALYZE CLUSTER COPY DELETE FETCH LISTEN MOVE
RESET SELECT TRUNCATE VACUUM
webdav'#
ABORT BEGIN COMMENT CREATE DROP GRANT LOAD
NOTIFY REVOKE SET UNLISTEN
ALTER CLOSE COMMIT DECLARE EXPLAIN INSERT LOCK
REINDEX ROLLBACK SHOW UPDATE
ANALYZE CLUSTER COPY DELETE FETCH LISTEN MOVE
RESET SELECT TRUNCATE VACUUM
webdav'# NEW.DiskAvailPC := disk_avail;

webdav'# NEW.DiskAvailPC := disk_avail;

webdav'# NEW.DiskAvailPC := disk_avail;
webdav'#
ABORT BEGIN COMMENT CREATE DROP GRANT LOAD
NOTIFY REVOKE SET UNLISTEN
ALTER CLOSE COMMIT DECLARE EXPLAIN INSERT LOCK
REINDEX ROLLBACK SHOW UPDATE
ANALYZE CLUSTER COPY DELETE FETCH LISTEN MOVE
RESET SELECT TRUNCATE VACUUM
webdav'# ELSE
webdav'#
ABORT BEGIN COMMENT CREATE DROP GRANT LOAD
NOTIFY REVOKE SET UNLISTEN
ALTER CLOSE COMMIT DECLARE EXPLAIN INSERT LOCK
REINDEX ROLLBACK SHOW UPDATE
ANALYZE CLUSTER COPY DELETE FETCH LISTEN MOVE
RESET SELECT TRUNCATE VACUUM
webdav'#
ABORT BEGIN COMMENT CREATE DROP GRANT LOAD
NOTIFY REVOKE SET UNLISTEN
ALTER CLOSE COMMIT DECLARE EXPLAIN INSERT LOCK
REINDEX ROLLBACK SHOW UPDATE
ANALYZE CLUSTER COPY DELETE FETCH LISTEN MOVE
RESET SELECT TRUNCATE VACUUM
webdav'#
ABORT BEGIN COMMENT CREATE DROP GRANT LOAD
NOTIFY REVOKE SET UNLISTEN
ALTER CLOSE COMMIT DECLARE EXPLAIN INSERT LOCK
REINDEX ROLLBACK SHOW UPDATE
ANALYZE CLUSTER COPY DELETE FETCH LISTEN MOVE
RESET SELECT TRUNCATE VACUUM
webdav'#
ABORT BEGIN COMMENT CREATE DROP GRANT LOAD
NOTIFY REVOKE SET UNLISTEN
ALTER CLOSE COMMIT DECLARE EXPLAIN INSERT LOCK
REINDEX ROLLBACK SHOW UPDATE
ANALYZE CLUSTER COPY DELETE FETCH LISTEN MOVE
RESET SELECT TRUNCATE VACUUM
webdav'# NEW.DiskAvailPC := disk_avail - (disk_avail * OLD.DiskUsed)
/ OLD.DiskAllocated;

webdav'# NEW.DiskAvailPC := disk_avail - (disk_avail * OLD.DiskUsed)
/ OLD.DiskAllocated;

webdav'# NEW.DiskAvailPC := disk_avail - (disk_avail * OLD.DiskUsed)
/ OLD.DiskAllocated;
webdav'#
ABORT BEGIN COMMENT CREATE DROP GRANT LOAD
NOTIFY REVOKE SET UNLISTEN
ALTER CLOSE COMMIT DECLARE EXPLAIN INSERT LOCK
REINDEX ROLLBACK SHOW UPDATE
ANALYZE CLUSTER COPY DELETE FETCH LISTEN MOVE
RESET SELECT TRUNCATE VACUUM
webdav'# ENDIF;
webdav'#
ABORT BEGIN COMMENT CREATE DROP GRANT LOAD
NOTIFY REVOKE SET UNLISTEN
ALTER CLOSE COMMIT DECLARE EXPLAIN INSERT LOCK
REINDEX ROLLBACK SHOW UPDATE
ANALYZE CLUSTER COPY DELETE FETCH LISTEN MOVE
RESET SELECT TRUNCATE VACUUM
webdav'#
webdav'#
ABORT BEGIN COMMENT CREATE DROP GRANT LOAD
NOTIFY REVOKE SET UNLISTEN
ALTER CLOSE COMMIT DECLARE EXPLAIN INSERT LOCK
REINDEX ROLLBACK SHOW UPDATE
ANALYZE CLUSTER COPY DELETE FETCH LISTEN MOVE
RESET SELECT TRUNCATE VACUUM
webdav'# RETURN NEW;
webdav'# END
webdav'# ' LANGUAGE 'plpgsql';
ERROR: language "plpgsql" does not exist
webdav=# \q

...so what doesn't it like here?

TIA,
Roy
--
--------------------
Roy MacGregor Paterson
MacGregorTech

t: +44 (0) 20 7584 7891
f: +44 (0) 20 7589 6223
m: +44 (0) 7803 163 938
w: www.macgregortech.com
e: roy(at)macgregortech(dot)com
--------------------