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

[Harald Fuchs <hari.fuchs@xxxxxxxxx>]

In article <CABRT9RDxHKCxrq8wboHnikpF-CGgkteJWdw3Q2_kXFEdP4prTw@xxxxxxxxxxxxxx>,
Marti Raudsepp <marti@xxxxxxxxx> 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.


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