Custom function execution on insert/update for field

[ishenkoyv]

Hi there I have geometry field, i.e. ->addField('location', 'public.geometry') and I want to make converting from text to geometry type on insert/update (but question is in general. this concrete situation is only an example). So the only solution I found is something like this

    protected function getParametersList(array $values)                         
    {                                                                           
        $parameters = [];                                                                                      

        foreach ($values as $name => $value) {                                                                 
            if ($name == 'location') {                                                                         
                $parameters[$name] = sprintf(                                                                  
                    "ST_PointFromText('POINT(' || $* || ')', 4326)::%s",        
                    $this->getStructure()->getTypeFor($name)                    
                );                                                                                             
                continue;                                                                                      
            }                                                                                                  

            $parameters[$name] = sprintf(                                                                      
                "$*::%s",                                                                                      
                $this->getStructure()->getTypeFor($name)                        
            );                                                                                                 
        }                                                                                                      

        return $parameters;                                                                                    
    }  

But it has drawbacks as two values are passed as single one so I can't separately escape each of them. The same situation with creation of of custom converter + I can't pass function with single quote in it. Maybe you know more elegant way to implement such on-fly converting of several values with functions

Thanks you

[chanmix51]

There are no built in converters yet for these kind of types. You have to write your own converter class (see ConverterInterface) and register it to the ConverterPooler in your SessionBuilder. I might complete the documentation about registering converters in a SessionBuilder. Another documentation here.

[ishenkoyv]

Problem not in converter itself (I have already created some of them). Let me explain Default behavior of getParameterList method is mapping fields to values in such way

sprintf(                                                                      
                "$*::%s",                                                                                      
                $this->getStructure()->getTypeFor($name)                        
            )

This means that field can be mapped to only one value. You use some workaround for Geometry types through additional Types (e.g. lib/Converter/Type/Point.php), but i my situation I want to use PG methods in fields definition (method call shouldn't be escaped) and escape two values (you use regexp to check values). Are you with me? Or I made problem from nothing? Maybe you can provide me with code that maps ST_PointFromText('POINT(' || $* || ' ' || $* || ')', 4326) to geometry field type?

Thank you

[chanmix51]

I think I understand. For now, basic query methods only accept values as parameters. If you want to use Postgresql's functions in your statements, you will have to write the query yourself in dedicated methods in the according Model class like the following:

public function updateSomething(Point $point)
{
    $sql = <<<SQL
update my_table set a_field = ST_PointFromText($*::point) where … returning :projection
SQL;
…
}

Is that what you want ?

[ishenkoyv]

So I can't use builtin methods like insertOne/updateOne and should write own custom sql?

[chanmix51]

For now yes. It used to be a RawString class in Pomm 1.x but it is not implemented in Pomm 2.0 because of the way values are converted. With Pomm 2, multiple insertions and update can re-use the same prepared statement which was not the case in Pomm 1. The drawback is not to be able to use Postgresql's functions. This problem will be addressed likely in Pomm 2.1.