How can I generate all trailing substrings following a delimeter?

Given a string that may contain multiple instances of a delimiter, I want to generate all substrings starting after that character.

For example, given a string like 'a.b.c.d.e' (or array {a,b,c,d,e}, I suppose), I want to generate an array like:

{a.b.c.d.e, b.c.d.e, c.d.e, d.e, e}

The intended usage is as a trigger to fill a column for easier querying of domain name parts (i.e. find all q.x.t.com for query t.com) whenever another column is written to.

It seems like an awkward way to solve this (and it very well may be), but now I'm curious how a function like this could be written in (Postgres') SQL.

These are email domain names so it's hard to say what the maximum possible number of elements is, but certainly the vast majority would be < 5.

I think this is my favorite.

create table t (id int,str varchar(100));
insert into t (id,str) values (1,'a.b.c.d.e'),(2,'xxx.yyy.zzz');

ROWS

select      id
           ,array_to_string((string_to_array(str,'.'))[i:],'.')

from        t,unnest(string_to_array(str,'.')) with ordinality u(token,i)
;

+----+-----------------+
| id | array_to_string |
+----+-----------------+
|  1 | a.b.c.d.e       |
|  1 | b.c.d.e         |
|  1 | c.d.e           |
|  1 | d.e             |
|  1 | e               |
|  2 | xxx.yyy.zzz     |
|  2 | yyy.zzz         |
|  2 | zzz             |
+----+-----------------+

ARRAYS

select      id
           ,array_agg(array_to_string((string_to_array(str,'.'))[i:],'.'))

from        t,unnest(string_to_array(str,'.')) with ordinality u(token,i)

group by    id
;

+----+-------------------------------------------+
| id |                 array_agg                 |
+----+-------------------------------------------+
|  1 | {"a.b.c.d.e","b.c.d.e","c.d.e","d.e","e"} |
|  2 | {"xxx.yyy.zzz","yyy.zzz","zzz"}           |
+----+-------------------------------------------+

StackExchange Database Administrators Q#157542, answer score: 5