Обсуждение: Installing procedural language(s)

I've running version 7.0.3 on a Red Hat 6.2 box (twin 500 MHz Celerons,
2.2.12-20smp kernel, 7.0.3 installed from rpms) and I've encountered a
bit of difficulty with functions and procedural languages while working
through Bruce's book.  Specifically, I've attempted to create the
"spread" function that is shown on page 211.  My input looks like this:

<snip>
johnb=# create function spread(text)
johnb-# returns text
johnb-# as 'DECLARE
johnb'#         str text;
johnb'#         ret text;
johnb'#         i   integer;
johnb'#         len integer;
johnb'#
johnb'#     BEGIN
johnb'#         str := upper($1);
johnb'#         ret := "";
johnb'#         i   := 1;
johnb'#         len := length(str);
johnb'#         WHILE i <= len LOOP
johnb'#            ret := ret || substr(str,i,1) || " ";
johnb'#            i := i + 1;
johnb'#         END LOOP;
johnb'#         RETURN ret;
johnb'#     END;'
johnb-# LANGUAGE 'plpgsql';
</snip>

When I "submit" the input, I receive the following error message:

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

This led me to believe that the "plpgsql" language was not installed.  I
went to the command line and entered "createlang -l -d johnb" and
received the following information:

<snip>
   Procedural languages
Name | Trusted? | Compiler
------+----------+----------
(0 rows)
</snip>

Seeing that there didn't seem to be any procedural languages installed,
I attempted to install "plpgsql" by entering "createlang plpgsql
johnb".  The system responded with the "Password:" prompt, at which
point I entered my password.  The system prompted for the password
again, so I entered it again.  Then the following was displayed (I've
included the prompts):

<snip>
[johnb@ophelia johnb]$ createlang plpgsql johnb
Password: Password: createlang: A function named 'plpgsql_call_handler'
already exists. Installation aborted.
</snip>

I should mention that the PGLIB environment variable is set to
"/usr/lib/pgsql" (which is where the "plpgsql.so" shared object is
located).  Could this be in error?

So I'm at an impass - I don't know what to try next.  Should I try the
"droplang" command and then attempt to "re-install"?

Thanks for your help.

--
John Burski
I.T. Manager and Systems Administration
911 Emergency Products, Inc.
25 Sixth Avenue North
Saint Cloud, MN  56303
John.Burski@911ep.com

800-863-6911, extension 221
FAX: 800-863-2991
www.911ep.com

John Burski <John.Burski@911ep.com> writes:
>  [ createlang problems ]

createlang does two steps: a CREATE FUNCTION command that adds a
"language handler" function, and then a CREATE LANGUAGE command
that references the function.  Apparently you have a database
in which the handler function already exists but the language
entry doesn't.  Recent versions of createlang try to cope with
this scenario but the 7.0.3 version wasn't very bright about it.

I'd suggest manually dropping the plpgsql_call_handler function,
and then createlang should run to completion.

It might be that you got into this state because of some unnoted
failure of a previous createlang attempt, so pay attention to
any error messages this time ...

            regards, tom lane

Thanks, Tom!

That did the trick!

I think what happened is that I wasn't expecting to be prompted for my
password four times.  IIRC, I Ctrl-C'd the process after the third
prompt the first time.  This time I decided to "humor" the process and
keep supplying the password until I wasn't prompted for it anymore or I
developed repetitive stress syndrome, whichever came first. :)

Thanks again!

Tom Lane wrote:

> John Burski <John.Burski@911ep.com> writes:
>
>>  [ createlang problems ]
>
>
> createlang does two steps: a CREATE FUNCTION command that adds a
> "language handler" function, and then a CREATE LANGUAGE command
> that references the function.  Apparently you have a database
> in which the handler function already exists but the language
> entry doesn't.  Recent versions of createlang try to cope with
> this scenario but the 7.0.3 version wasn't very bright about it.
>
> I'd suggest manually dropping the plpgsql_call_handler function,
> and then createlang should run to completion.
>
> It might be that you got into this state because of some unnoted
> failure of a previous createlang attempt, so pay attention to
> any error messages this time ...
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>

--
John Burski
I.T. Manager and Systems Administration
911 Emergency Products, Inc.
25 Sixth Avenue North
Saint Cloud, MN  56303
John.Burski@911ep.com

800-863-6911, extension 221
FAX: 800-863-2991
www.911ep.com