4

I added an extension and my install shows the module installed,

mydb=# \dx
                                   List of installed extensions
 Name   | Version |   Schema   |                                  Description                            
--------+---------+------------+-------------------------------------------------------------------
pg_trgm | 1.0     | extensions | text similarity measurement and index searching based on trigrams
plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

But when I go to use it I'm getting an error,

mydb=# select similarity('hello','hell');
ERROR:  function similarity(unknown, unknown) does not exist
LINE 1: select similarity('hello','hell');
           ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Is there any other step to use the functions the extension provides?

Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
Emerson R Orci
  • 85
  • 1
  • 6

2 Answers2

3

It should work. Here a psql protocol: (sorry, system is in german)

str@s131-intel:~> psql
psql (9.2.7)

strobel=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION
strobel=# \df
                                                          Liste der Funktionen
 Schema |          Name          | Ergebnisdatentyp |                            Argumentdatentypen                             |  Typ   
--------+------------------------+------------------+---------------------------------------------------------------------------+--------
 public | flipper                | boolean          |                                                                           | normal
 public | gin_extract_query_trgm | internal         | text, internal, smallint, internal, internal, internal, internal          | normal
 public | gin_extract_value_trgm | internal         | text, internal                                                            | normal
 <cut>
 public | gtrgm_union            | integer[]        | bytea, internal                                                           | normal
 public | set_limit              | real             | real                                                                      | normal
 public | show_limit             | real             |                                                                           | normal
 public | show_trgm              | text[]           | text                                                                      | normal
 public | similarity             | real             | text, text                                                                | normal
 public | similarity_dist        | real             | text, text                                                                | normal
 public | similarity_op          | boolean          | text, text                                                                | normal
(20 Zeilen)

strobel=# select similarity('hallo', 'hello');
 similarity 
------------
   0.333333
(1 Zeile)

The error message gives a hint that your parameters were not seen as text, so maybe you wrote a backtick (accent grave) instead of apostrophe.

Str.
  • 1,389
  • 9
  • 14
3

The module was installed into the schema extensions. I guess that that schema is not in your search path and therefor the function is not found. Try

select extensions.similarity('hello','hell');

If you don't want to fully-qualify the function names you can simply add that schema to your search_path:

alter user your_pg_user set search_path = public,extensions;

If that still gives an error your Postgres user might not have the necessary privileges to call the functions. In that case log in as the superuser (typically postgres) and grant the priviliges:

grant all on schema extensions to your_pg_user;