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

> -----Mensagem original-----
> De: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] Em nome de Harald Fuchs
> Enviada em: terça-feira, 27 de setembro de 2011 07:01
> Para: pgsql-general(at)postgresql(dot)org
> Assunto: Re: [GENERAL] 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.

Upgrade to 9.1 into production servers is not na option.

It will take about a year before I can migrate all databases and establish
replication and everything else (probably, Christams Holidays in December or
Carnival in February next).

Some users demand 24x7 from this databases, I can't just say "stop for
one-two hour".

Thanks for the tip, I'll give a try for both funtions and let you know the
results. Would save time for future...

Regards,

Edson.

>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general