PostgreSQL procedural language “C” not found

问题:

I am trying to use the PL/R procedural language in a PostgreSQL 9.2 database. I have installed the plr language and I am trying to add it to a database. When I run the command CREATE EXTENSION plr; I get the following error:

ERROR:  language "C" does not exist
STATEMENT:  CREATE EXTENSION plr;
ERROR:  language "C" does not exist

When I list the available languages in the database with select * from pg_language; I get

 lanname  | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl 
 ----------+----------+---------+--------------+---------------+-----------+--------------+--------
  internal |       10 | f       | f            |             0 |         0 |         2246 | 
  c        |       10 | f       | f            |             0 |         0 |         2247 | 
  sql      |       10 | f       | t            |             0 |         0 |         2248 | 
  plpgsql  |       10 | t       | t            |         12514 |     12515 |        12516 | 
 (4 rows)

So there is a language c but it is not in capital letters (not sure if that makes a difference).

I am wondering why the plr extension does not find the C procedural language?

回答1:

You are probably running into this change in PostgreSQL 9.2 (quoting the release notes here):

No longer forcibly lowercase procedural language names in CREATE FUNCTION (Robert Haas)

While unquoted language identifiers are still lowercased, strings and quoted identifiers are no longer forcibly down-cased. Thus for example CREATE FUNCTION ... LANGUAGE 'C' will no longer work; it must be spelled 'c', or better omit the quotes.

It's also reflected in the manual for CREATE FUNCTION

lang_name

The name of the language that the function is implemented in. Can be SQL, C, internal, or the name of a user-defined procedural language. For backward compatibility, the name can be enclosed by single quotes.

Quoting the language name has been discouraged since at least version 7.3 (maybe longer), but old habits die hard, obviously. Removing the quotes around 'C' fixes the problem, arriving at: LANGUAGE c or LANGUAGE C.

PL/R wasn't ready for PostgreSQL 9.2 in that respect, judging from the project page.

Feedback from Joe Conway

Joe Conway left an answer that got deleted because it should be a comment. I paste it here for the general public who can't see deleted answers:

I got the message, just haven't had the time to do a new PL/R release. Look for it by December, but in the meantime the manual workaround noted above is pretty simple.

回答2:

I had a similar issue with pg_collkey. I believe your situation may have the same solution. Here's what I did: There's a .sql file in your postgres extension directory that tells postgres how to install the extension. In my case, it's called pg_collkey--0.5.0.sql. You'll need to modify that sql file. On my system (using Mac OS X and Homebrew) the file is located at /usr/local/share/postgresql/extension/pg_collkey--0.5.0.sql). It probably contains a capital "C" in the LANGUAGE clause, like so:

CREATE OR REPLACE FUNCTION collkey (text, text, bool, int4, bool) RETURNS bytea
  LANGUAGE 'C' IMMUTABLE STRICT AS
  '$libdir/collkey_icu.so',
  'pgsqlext_collkey';

Change it to a lower-case 'c' and re-run the "CREATE EXTENSION pg_collkey;" command in psql:

psql -c 'CREATE EXTENSION pg_collkey;' my_database_name

Of course, you'll need to use "plr" instead of pg_collkey for the extension name.