Re: Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries? - Mailing list pgsql-general

On Tue, Sep 27, 2011 at 01:43, Edson Carlos Ericksson Richter
<richter@simkorp.com.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