Thread:
Hi all, I was trying to create function in postgres, but it returns error mentioning the language is NOT defined. The function is as following: CREATE OR REPLACE FUNCTION test_word_count(TEXT, TEXT) RETURNS INTEGER AS $$ DECLARE d_word ALIAS FOR $1; d_phrase ALIAS FOR $2; BEGIN IF d_word IS NULL OR d_phrase IS NULL THEN RETURN 0; RETURN 1; END; $$ LANGUAGE plpgsql; ERROR: language "plpgsql" does not exist HINT: Use CREATE LANGUAGE to load the language into the database. I was wonderring why it is not included by default? Or have I missed out something in the configuration! Also, how to do a better text search? I have come across the bad performance of LIKE statement. All helps are appreciated. Thanks. _________________________________________________________________ Manage multiple email accounts with Windows Live Mail! http://www.get.live.com/wl/all
carter ck wrote: > Hi all, > > I was trying to create function in postgres, but it returns error mentioning the language is NOT defined. > > The function is as following: > > CREATE OR REPLACE FUNCTION test_word_count(TEXT, TEXT) RETURNS INTEGER AS $$ > DECLARE > d_word ALIAS FOR $1; > d_phrase ALIAS FOR $2; > BEGIN > IF d_word IS NULL OR d_phrase IS NULL THEN RETURN 0; > > RETURN 1; > > END; > > $$ LANGUAGE plpgsql; > > ERROR: language "plpgsql" does not exist > HINT: Use CREATE LANGUAGE to load the language into the database. > > I was wonderring why it is not included by default? Or have I missed out something in the configuration! > > According to the documentation, you have to explicitly create the language in order to register the language with the database: CREATE LANGUAGE plpgsql; Assuming everything else is set up properly, this will allow you to use the plpgsql language. Ron
On Wed, Oct 31, 2007 at 11:07:36AM +0800, carter ck wrote: > Hi all, > > I was trying to create function in postgres, but it returns error mentioning the language is NOT defined. <snip> > ERROR: language "plpgsql" does not exist > HINT: Use CREATE LANGUAGE to load the language into the database. > > I was wonderring why it is not included by default? Or have I missed out something in the configuration! It's included by default, just not enabled by default. Try "create language plpgsql" as administrator. > Also, how to do a better text search? I have come across the bad performance of LIKE statement. See tsearch2. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 >>> On Wed, Oct 31, 2007 at 4:07 AM, in message <BAY101-W1511B7A0AA605D61D8630BD5930@phx.gbl>, carter ck <carterck32@hotmail.com> wrote: > Hi all, > > I was trying to create function in postgres, but it returns error mentioning > the language is NOT defined. > > The function is as following: > > CREATE OR REPLACE FUNCTION test_word_count(TEXT, TEXT) RETURNS INTEGER AS $$ > DECLARE > d_word ALIAS FOR $1; > d_phrase ALIAS FOR $2; > BEGIN > IF d_word IS NULL OR d_phrase IS NULL THEN RETURN 0; > > RETURN 1; > > END; > > $$ LANGUAGE plpgsql; > > ERROR: language "plpgsql" does not exist > HINT: Use CREATE LANGUAGE to load the language into the database. > > I was wonderring why it is not included by default? Or have I missed out > something in the configuration! > > Also, how to do a better text search? I have come across the bad performance > of LIKE statement. Have you tried TSearch2 to do a text search?
You can also create the language in template1 and then you'll have it in any other database you'll create (from template1). Il Wednesday 31 October 2007 08:21:08 Martijn van Oosterhout ha scritto: > On Wed, Oct 31, 2007 at 11:07:36AM +0800, carter ck wrote: > > Hi all, > > > > I was trying to create function in postgres, but it returns error > > mentioning the language is NOT defined. > > <snip> > > > ERROR: language "plpgsql" does not exist > > HINT: Use CREATE LANGUAGE to load the language into the database. > > > > I was wonderring why it is not included by default? Or have I missed out > > something in the configuration! > > It's included by default, just not enabled by default. Try "create > language plpgsql" as administrator. > > > Also, how to do a better text search? I have come across the bad > > performance of LIKE statement. > > See tsearch2. > > Have a nice day, -- Reg me Please