Thread: Failed to create a function

Failed to create a function

From

Roy MacGregor Paterson

Date:

25 November 2003, 15:03:33

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@macgregortech.com
--------------------

Re: Failed to create a function

From

Stephan Szabo

Date:

25 November 2003, 15:44:53

On Tue, 25 Nov 2003, Roy MacGregor Paterson wrote:

> webdav'# ' LANGUAGE 'plpgsql';
> ERROR:  language "plpgsql" does not exist

You should use the createlang script to create the plpgsql handler in the
database in question (if you want future databases to automatically get
it, you may wish to also create it into template1).

Re: Failed to create a function

From

Roy MacGregor Paterson

Date:

25 November 2003, 19:21:00

>You should use the createlang script to create the plpgsql handler in the
>database in question (if you want future databases to automatically get
>it, you may wish to also create it into template1).

Many thanks, Stephan.

I had misunderstood the line in the book "...([PL/pgSQL] is installed
with PostgreSQL by default)..." and dived straight into the code,
which I've never done before ;-)

regards,
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@macgregortech.com
--------------------

Failed to create a function SOLVED

From

Roy MacGregor Paterson

Date:

03 December 2003, 07:27:21

Thanks to all who helped out on this problem.

RTFM and trial by Kafka-esque error are truly wonderful things.

One of the problems I was having with creating functions was the
strange behaviour of psql (responding with a list of command options
for every line pasted) when I was copying and pasting them from
BBEdit files, like this snippet...

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

...it turns out that, despite comments about psql being quite happy
with all whitespace, it's not: it doesn't like tab chars (in PG 7.3.3
on Mac OS X at least). Convert tabs to 2xspaces and all is
tickety-boo.

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@macgregortech.com
--------------------

Re: Failed to create a function SOLVED

From

Tom Lane

Date:

03 December 2003, 15:04:33

Roy MacGregor Paterson <roy@macgregortech.com> writes:
> ...it turns out that, despite comments about psql being quite happy
> with all whitespace, it's not: it doesn't like tab chars

Sure it does: it thinks they are requests for keyword completion.

You can turn this off (along with most other input editing features)
with the -n switch.  There are more selective ways too I believe,
but I don't have the readline manual in my head ...

            regards, tom lane