Symfony2 - Custom DQL function registered but does not exist

Question

I am desperately trying to enable the LEVENSHTEIN function in Symfony2, however, I am still getting errors. Specifications + what I have done so far:

• PostgreSQL 9.3
• LEVENSHTEIN is included in the fuzzystrmatch extension

• Function tested through shell execution. Works great:

  postgres=# SELECT levenshtein('test', 'text'); levenshtein ------------- 1 (1 row) 

• Added function in DQL:

   <?php namespace AppBundle\DQL; use Doctrine\ORM\Query\AST\Functions\FunctionNode; use Doctrine\ORM\Query\Lexer; use Doctrine\ORM\Query\Parser; use Doctrine\ORM\Query\SqlWalker; class LevenshteinFunction extends FunctionNode { public $firstStringExpression = null; public $secondStringExpression = null; public function getSql(SqlWalker $sqlWalker) { return 'LEVENSHTEIN(' . $this->firstStringExpression->dispatch($sqlWalker) . ', ' . $this->secondStringExpression->dispatch($sqlWalker) . ')'; } public function parse(Parser $parser) { // levenshtein(str1, str2) $parser->match(Lexer::T_IDENTIFIER); $parser->match(Lexer::T_OPEN_PARENTHESIS); $this->firstStringExpression = $parser->StringPrimary(); $parser->match(Lexer::T_COMMA); $this->secondStringExpression = $parser->StringPrimary(); $parser->match(Lexer::T_CLOSE_PARENTHESIS); } } 

  config.yml

   orm: auto_generate_proxy_classes: "%kernel.debug%" auto_mapping: true dql: numeric_functions: LEVENSHTEIN: AppBundle\DQL\LevenshteinFunction 

• Problem . When executing the following code, the following errors occur in my repository:

   $this->getEntityManager()->createQuery("SELECT LEVENSHTEIN('test', 'text') FROM AppBundle:User"); return $query->getResult(); 

  SQLSTATE [42883]: Undefined function: 7 ERROR: levenshtein function (unknown, unknown) does not exist

What am I missing? Why doesn't DQL / Symfony / PDO / ... recognize a function? Any help is much appreciated!

Answer 1

The error comes from Postgres, it seems like a visibility issue.

The optional fuzzystrmatch module must be installed, of course. You obviously did this, or your function call will not work in psql either.

• How to exclude PL / pgSQL functions during export?

If it works in psql but not in your application, there are only a few possible explanations. The obvious first:

• Are you connected to the same database? (Same server, same port, same db?)

• Are you connecting to one user? Probably not ...

• If you are connecting to another user (but in any case), check if you are working with the same search path. Run in any connection and compare:

   SHOW search_path; 

  Details - and how to set search_path :

  • How affects the identifier of the search identifier and the "current scheme"

Keep in mind that extensions can be installed in any circuit of your choice. By default, this is the first scheme in search_path (the "current scheme" at the time of installation, which is usually public , but I do not know about this installation. Documentation:

If not specified, and the extension control file does not specify a scheme, or the current default object creation scheme is used.

Run this to diagnose a few things:

 SELECT e.extname AS extension, nsp.nspname AS schema , r.rolname AS schema_owner, nsp.nspacl AS schema_acl FROM pg_extension e JOIN pg_namespace nsp ON nsp.oid = e.extnamespace JOIN pg_roles r ON r.oid = nsp.nspowner 

You get something like:

  extension | schema | schema_owner | schema_acl ---------------+------------+--------------+------------------------------------- adminpack | pg_catalog | postgres | {postgres=UC/postgres,=U/postgres} plpgsql | pg_catalog | postgres | {postgres=UC/postgres,=U/postgres} fuzzystrmatch | public | postgres | {postgres=UC/postgres,=UC/postgres} tablefunc | public | postgres | {postgres=UC/postgres,=UC/postgres} ... 

If schema_acl includes =U/postgres ( U for USAGE ), then the public role has access, i.e. all.

Set search_path for your connection accordingly or (re) set it to the visible scheme and it should work.

Theoretically, the owning role or superuser can revoke the EXECUTE permission of the function itself ...

Answer 2

Your feature class seems convenient to me, but your configuration may be wrong. This is what I have for my CAST function:

 doctrine: orm: dql: string_functions: CAST: App\MyBundle\Doctrine\DBAL\Functions\Porgres\Cast 

You should notice that you have different collections for different types of functions, i.e. string_functions , numeric_functions , datetime_functions . All of them are listed in the official documentation .

In addition, your code should work fine after clearing the cache.