Trouble with createlang plpgsql

[Kathryn Hayes]

You guys are going to have to bear with me here, because this is my
first time trying to administer a database. I'm having trouble with
the 'createlang plpgsql' command. When I run the command on the xnat
database, it returns without an error, but when I run xnat.sql, it
still can't find plpgsql. What am I doing wrong here?

Thanks,

Katie

[postgres@slicerl sql]$ createdb xnat
CREATE DATABASE
[postgres@slicerl sql]$ createlang plpgsql xnat
[postgres@slicerl sql]$ psql
Welcome to psql 8.1.3, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postgres=# \c xnat
You are now connected to database "xnat".
xnat=# \i xnat
[... lots of output here...]
CREATE VIEW
CREATE TABLE
GRANT
psql:xnat.sql:3314: ERROR: language "plpgsql" does not exist
HINT: Use CREATE LANGUAGE to load the language into the database.
psql:xnat.sql:3343: ERROR: language "plpgsql" does not exist
HINT: Use CREATE LANGUAGE to load the language into the database.
psql:xnat.sql:3357: ERROR: language "plpgsql" does not exist
HINT: Use CREATE LANGUAGE to load the language into the database.
psql:xnat.sql:3376: ERROR: language "plpgsql" does not exist
HINT: Use CREATE LANGUAGE to load the language into the database.
psql:xnat.sql:3396: ERROR: language "plpgsql" does not exist
HINT: Use CREATE LANGUAGE to load the language into the database.
xnat=#

[Mohana]

Please execute this command to test if the plpgsql language was created
successfully or not:

createlang -l -d xnat -U <postgres user name>

If plpgsql was created succesfully, the above command should display
something like:

Procedural Languages
Name | Trusted?
---------+----------
plpgsql | <no|yes>

There are two ways in which a language can be created. One is using
createlang and the other is using CREATE LANGUAGE SQL statement.
createlang is a wrapper for the second method.

CREATE LANGUAGE is the SQL command which adds procedural languages to
the currently connected database. Before it can be used, however, the
CREATE FUNCTION command must first be used to create the procedural
call handler.

Here are the steps to install using second method:

1. Launch psql and connect to db xnat

2. Execute
CREATE OR REPLACE FUNCTION plpgsql_call_handler()
RETURNS language_handler AS
'$libdir/plpgsql', 'plpgsql_call_handler'
LANGUAGE 'c' VOLATILE;

$libdir is a PostgreSQL representation of the directory specified as
the path to the shared objects library when PostgreSQL was configured.
This path, by default, is /usr/local/pgsql/lib/. You could find out the
path to the library files by executing pg_config --pkglibdir.

3. Execute
CREATE PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler;

4. Test if the creation of language was successfull.

Let us know if you have any problems

~Mohana

[Kathryn Hayes]

Thank you very much for your help. Steps 2 and 3 allowed me to create
the language, and I was able to run xnat.sql.

Katie

[Tim Olsen]

That's a new exception. We'll have to delve into that one.

What Operating System are you on?

Thanks,
Tim

[Kathryn Hayes]

I don't know if your last post was intended tto go here or in the
StoreXML thread, but either way, I'm running RHEL 4.

Katie

[Rick Herrick]

Hey Len,

With Postgres 9.1, you shouldn't have to do the createlang step. Databases created on 9.0 or later (in fact, I think 8.4 or later, but am not sure) have plpgsql installed by default.

You can verify the presence of plpgsql pretty easily in pgadmin3 by clicking into your database and expanding the Extensions node. If plpgsql is installed, you should see it listed there. You can also check it through the psql client:

xnat-> \dx

                 List of installed extensions

  Name   | Version |   Schema   |         Description

---------+---------+------------+------------------------------

 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language

(1 row)

Any other extensions installed will also be shown here (e.g. postgis).

Lastly you could also just try running the XNAT SQL script. If you DON'T have plpgsql installed, the script will fail and then you'll know!

On Monday, December 3, 2012 10:10:57 AM UTC-6, Len wrote:

Hi, I got the same problem. But this resolution does not work for me.
I'm trying to configure XNat 1.6. on openSUSE 12.1 using postgre 9.1

Op dinsdag 14 maart 2006 06:00:52 UTC+1 schreef Mohana het volgende: