Thread: Trigger using function written in PL/PGSQL returns "Unrecognized language specified"

I am writing my first trigger & function.

First question:
---------------
I can't get psql to take the function declaration.

ds=# > CREATE FUNCTION trigger_insert_update_copay()
ds-# RETURNS opaque

ds-# RETURNS opaque AS 'BEGIN
ds'# new.copay = (new.allowable * new.percent);
ds'# RETURN new;
ds'# END;'
ds-# LANGUAGE 'plpgsql'
ds-# Error: ERROR:  Unrecognized language specified in a CREATE FUNCTION:
'plpgsql'.  Recognized languages are sql, C, internal and the created
procedural languages.

Here is my function:
CREATE FUNCTION trigger_insert_update_copay()
RETURNS opaque          -- should this be NUMERIC(??) or FLOAT4?
AS 'BEGIN
         new.copay = (new.allowable * new.percent);
         RETURN new;
END;'
LANGUAGE 'plpgsql';

Here is my Trigger:
CREATE TRIGGER trigger_copay
BEFORE INSERT
ON coverage
FOR EACH ROW
EXECUTE PROCEDURE trigger_insert_update_copay();


Second question:
----------------
It is supposed to multiply the 'allowable' and 'percent' field and leave
the result in 'copay'.  Is
         new.copay = (new.allowable * new.percent);
         RETURN new;
The correct code to do that?

FYI:

[root@ds /root]# rpm -q -a | grep post
postgresql-devel-7.0.3-2
postgresql-jdbc-7.0.3-2
postgresql-tcl-7.0.3-2
postgresql-tk-7.0.3-2
postgresql-7.0.3-2
postgresql-odbc-7.0.3-2
postgresql-perl-7.0.3-2
postgresql-python-7.0.3-2
postgresql-server-7.0.3-2
postgresql-test-7.0.3-2
[root@ds /root]#

Dan Browning, Cyclone Computer Systems, danb@cyclonecomputers.com

From: "Dan B" <db@cyclonehq.dnsalias.net>

> I am writing my first trigger & function.

I'm about a week ahead of you, so if we were using MS products I'd be a
qualified trainer by now.

> First question:
> ---------------
> I can't get psql to take the function declaration.
>
> ds=# > CREATE FUNCTION trigger_insert_update_copay()
...
> ds-# LANGUAGE 'plpgsql'
> ds-# Error: ERROR:  Unrecognized language specified in a CREATE FUNCTION:
> 'plpgsql'.  Recognized languages are sql, C, internal and the created
> procedural languages.

You need to run "createlang plpgsql dbname" on the database first - man
createlang


> Here is my function:

Everything looks fine to the naked eye - one createlang and you're in
business I'd say.

- Richard Huxton

Dan B <db@cyclonehq.dnsalias.net> wrote:
>ds-# LANGUAGE 'plpgsql'
>ds-# Error: ERROR:  Unrecognized language specified in a CREATE FUNCTION:
>'plpgsql'.  Recognized languages are sql, C, internal and the created
>procedural languages.

It looks like you haven't used createlang(1) to add plpgsql to the languages
recognised by the database you're working on.

HTH,
Ray
--
"Perhaps they spent some of the time writing the patent application. That
task was surely harder than thinking of the technique."
    RMS on Amazon's 1-Click(R) patent,
    http://linuxtoday.com/story.php3?sn=13652