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!

score 1 · Answer 1 · answered Nov 23 '15 at 14:50

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.

score 1 · Accepted Answer · edited May 23 '17 at 10:30

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 ...

Symfony2 - Custom DQL Function Registered But Does Not Exist

2 Answers2