3

Looking for a Postgres 9.6 function that reverse sorts a string the same way that java namespaced packages do. Preferable if this is just PGSQL, I know that this is simple in the V8 engine or Python.

I.e. if I have the string foo.bar.baz.example.com, I want the string com.example.baz.bar.foo. My very naive attempts like running a REVERSE on the string didn't work.

I'm thinking I would need to explode on the periods, (can you explode in Postgres?), start a cursor, loop over the set, insert the last item from the set, shift that item off the set, and continue until the set is empty.

But I don't know how to do any of that.

Improve this question

2 Answers 2

Reset to default
4

You can do this with a combination of array functions:

select string_agg(x, '.' order by idx desc)
from unnest(string_to_array('foo.bar.baz.example.com','.')) with ordinality as t(x, idx);

you can put this into a function if you want:

create function reverse_elements(p_words text)
  returns text
as
$$
  select string_agg(x, '.' order by idx desc)
  from unnest(string_to_array(p_words,'.')) with ordinality as t(x, idx);
$$
language sql;
Improve this answer
1
  • Lifesaver. I tried this: CREATE OR REPLACE FUNCTION REVERSE_SORT_DOTS(unordered_string TEXT) RETURNS TEXT AS $$ BEGIN DECLARE new_string TEXT DEFAULT ''; arr varchar[] := SELECT string_to_array(REVERSE(unordered_string), '.'); FOREACH element IN ARRAY arr new_string := CONCAT(element, '.', new_string); LOOP END LOOP; RETURN new_string; END; $$ LANGUAGE plpgsql; But it didn't work. Commented Jun 1, 2017 at 18:32
1

Using plperl

String modification is really slow when you're using string_agg, and unnest and ordinality. You'll find plperl to be over twice as fast as the sql method.

CREATE FUNCTION reverse_perl(p_words text)
  RETURNS text
AS
$$
  return join ".", reverse split /\./, $_[0];
$$
LANGUAGE plperl
STRICT
IMMUTABLE;

Benchmark,

test=# EXPLAIN ANALYZE SELECT reverse_perl( array_to_string(ARRAY[x,x,x,x], '.') ) FROM generate_series(1,100) AS gs(x);
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series gs  (cost=0.00..262.50 rows=1000 width=4) (actual time=0.104..1.477 rows=100 loops=1)
 Planning time: 0.054 ms
 Execution time: 1.519 ms
(3 rows)

Time: 2.030 ms
test=# EXPLAIN ANALYZE SELECT reverse_elements( array_to_string(ARRAY[x,x,x,x], '.') ) FROM generate_series(1,100) AS gs(x);
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series gs  (cost=0.00..262.50 rows=1000 width=4) (actual time=0.236..4.161 rows=100 loops=1)
 Planning time: 0.147 ms
 Execution time: 4.208 ms
(3 rows)

Time: 4.770 ms

Granted, this is a pretty simple query and it probably doesn't matter for most reasonable use cases.

Improve this answer
2
  • 1
    Very helpful. I tried the PGSQL method and it was slower than doing the same thing natively (Python), which I assumed was because postgres probably doesn't optimize arrays as much as Python. Commented Jun 2, 2017 at 3:47
  • @user3112092 plpython, because if the failures of python is not trusted in PostgreSQL. I grab plv8 or plperl depending on the task. Plperl is usually faster with string manipulation. Commented Jun 2, 2017 at 5:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.