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

In article <CABRT9RDxHKCxrq8wboHnikpF-CGgkteJWdw3Q2_kXFEdP4prTw(at)mail(dot)gmail(dot)com>,
Marti Raudsepp <marti(at)juffo(dot)org> writes:

> 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$$;

Pavel Stehule has found a better solution for that:

CREATE OR REPLACE FUNCTION reverse(text) RETURNS text AS $$
SELECT string_agg(substring($1 FROM i FOR 1), '')
FROM generate_series(length($1), 1, -1) g(i)
$$ language sql;

But the best, of course, is upgrading to 9.1.