Symfony2 - Custom DQL Function Registered But Does Not Exist

Question

I am desperatelly trying to include the LEVENSHTEIN function in Symfony2, however, I still receive errors. Specs + what I've done so far:

• PostgreSQL 9.3
• LEVENSHTEIN included in fuzzystrmatch extension

• Tested the function via shell execution. Works perfectly fine:

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

• Added the 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 codeblock in my Repository, the following errors occur:

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

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

What am I missing? Why isn't DQL/Symfony/PDO/... recognizing the function? Any help is highly appreciated!

Answer 1

The error comes from Postgres, seems like a problem with visibility.

The additional module fuzzystrmatch has to be installed, of course. You obviously did that, or your function call would not work in psql either.

• How to exclude PL/pgSQL functions in export?

If it works in psql, but not in your app, only a few possible explanations remain. The obvious first:

• You are connected to the same database? (Same server, same port, same db?)

• You are connecting with the same user? Probably not ...

• If connecting with a different user (but in any case), check whether you are working the same search path. Run in either connection and compare:

  SHOW search_path;
  

  Details - and how to set the search_path:

  • How does the search_path influence identifier resolution and the "current schema"

Be aware that extensions can be installed to any schema of your choice. Default is the first schema in the search_path (the "current schema" at the time of the install, which is typically public, but I don't know that about your installation. The documentation:

If not specified, and the extension's control file does not specify a schema either, the current default object creation schema is used.

Run this to diagnose a couple of 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. everybody.

Set the search_path for your connection accordingly or (re-)install to a visible schema and it should work.

Theoretically, the owning role or a superuser might have revoked the EXECUTE permission from the function itself ...

Answer 2

Your function class seems ok to me, but your configuration might 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 note that you have different collections for the different types of functions i.e. string_functions,numeric_functions, datetime_functions. All of them are listed in the official documentation.

Other than that, your code should be working just fine after you clean your cache.