Thread: Trouble with CREATE FUNCTION

Dear All,

I have tried creating a function as follows:

CREATE FUNCTION chkTelephone () RETURNS OPAQUE AS '  BEGIN     IF EXISTS (SELECT *                FROM tenant t
      WHERE t.areacode = NEW.areacode AND                      t.telephone = NEW.telphone) THEN        RAISE EXCEPTION
''THATTELEPHONE NUMBER ALREADY EXISTS!'';     END IF;     RETURN NEW;  END;
 
' LANGUAGE 'plpgsql';

However I get the following error:

ERROR:  Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'.
Recognized languages are sql, C, internal and the created procedural
languages.

In the documentation it states that 'PL/pgSQL is a loadable procedural
language for the Postgres database system.'. Where can I load it from and
are there any other problems that may arise?

Cheers, Steve.

1.The following command tells the database where to find the shared object
for the PL/pgSQL language's call handler function. 
     CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS         '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';
        
 

2.The command 
     CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'         HANDLER plpgsql_call_handler         LANCOMPILER 'PL/pgSQL';
        
 

Jie LIANG

St. Bernard Software
Internet Products Inc.

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang@ipinc.com
www.stbernard.com
www.ipinc.com

On Sun, 18 Feb 2001, S Bey wrote:

> Dear All,
> 
> I have tried creating a function as follows:
> 
> CREATE FUNCTION chkTelephone () RETURNS OPAQUE AS '
>    BEGIN
>       IF EXISTS (SELECT *
>                  FROM tenant t
>                  WHERE t.areacode = NEW.areacode AND
>                        t.telephone = NEW.telphone) THEN
>          RAISE EXCEPTION ''THAT TELEPHONE NUMBER ALREADY EXISTS!'';
>       END IF;
>       RETURN NEW;
>    END;
> ' LANGUAGE 'plpgsql';
> 
> However I get the following error:
> 
> ERROR:  Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'.
> Recognized languages are sql, C, internal and the created procedural
> languages.
> 
> In the documentation it states that 'PL/pgSQL is a loadable procedural
> language for the Postgres database system.'. Where can I load it from and
> are there any other problems that may arise?
> 
> Cheers, Steve.
>