I would like to:
* split a string by parts
* reverse the order of the parts
* reassemble the parts in reverse order
By way of example...
From experience with a similar situation on another platform, I don't expect that the above implementation will rigorously preserve ordering. [^1]^,^[^2]
1. Is this assumption correct?
2. Is there a better implementation of by-parts-reversal that _does_ rigorously preserve ordering?
If you want to guarantee ordering (and you should), you can use [with ordinality](https://www.postgresql.org/docs/12/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS) since version 9.4, along with an aggregate with [order by](https://www.postgresql.org/docs/12/sql-expressions.html#SYNTAX-AGGREGATES):
> From experience with a similar situation on another platform, I don't expect that the above implementation will rigorously preserve ordering.
> 1. Is this assumption correct?
At the very least, given that the ordering isn't otherwise guaranteed, you run the risk that a future version of postgres will return the results in a different order.
You will probably find edge-cases where the ordering breaks down (parallel queries for example?) even on current versions. I guess you have to ask if "[usually works](https://www.postgresql.org/docs/12/functions-aggregate.html)" is good enough for you:
> This ordering is unspecified by default, but can be controlled by writing an `ORDER BY` clause within the aggregate call, as shown in Section 4.2.7. Alternatively, supplying the input values from a sorted subquery will ***usually work*** …
I would probably use a scalar sub-select:
(select string_agg(e, '.' order by idx desc)
from unnest(string_to_array(bar, '.')) with ordinality as t(e,idx)) as bar
or maybe create a function to make this easier:
create or replace function reverse_elements(p_list text, p_delimiter text default '.')
select string_agg(e, p_delimiter order by idx desc)
from unnest(string_to_array(p_list, p_delimiter)) with ordinality as t(e,idx);
Then it's as simple as: