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

From Harald Fuchs
Subject Re: Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?
Date
Msg-id 86r532l2xy.fsf@protecting.net
Whole thread Raw
In response to Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?  ("Edson Carlos Ericksson Richter" <richter@simkorp.com.br>)
Responses Re: Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?  (Marti Raudsepp <marti@juffo.org>)
RES: Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?  ("Edson Carlos Ericksson Richter" <richter@simkorp.com.br>)
List pgsql-general
In article <CABRT9RDxHKCxrq8wboHnikpF-CGgkteJWdw3Q2_kXFEdP4prTw@mail.gmail.com>,
Marti Raudsepp <marti@juffo.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.

pgsql-general by date:

Previous
From: Venkat Balaji
Date:
Subject: Re: PostgreSQL recovery when lost some file in data\global
Next
From: Marti Raudsepp
Date:
Subject: Re: Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?