Re: Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?

On Tue, Sep 27, 2011 at 01:43, Edson Carlos Ericksson Richter
<richter(at)simkorp(dot)com(dot)br> wrote:
>> create index on foobar (txt text_pattern_ops); create index on foobar
>> (reverse(txt) text_pattern_ops);
>
> I got the following error:
>
> ERROR: function reverse(text) does not exist
> Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Ah, the reverse() function is not included with PostgreSQL 9.0 yet.
This is what I use:

CREATE FUNCTION reverse(input text) RETURNS text
LANGUAGE plpgsql IMMUTABLE STRICT AS $$
DECLARE
result text = '';
i int;
BEGIN
FOR i IN 1..length(input) BY 2 LOOP
result = substr(input,i+1,1) || substr(input,i,1) || result;
END LOOP;
RETURN result;
END$$;

Regards,
Marti