A few ways, some better than others.
I general I advocate learning to think in sets when working with SQL and relational databases. JOINs start making lots of sense when you think of them as operations on sets. So do filters like WHERE and GROUP BY. You'll often find that you can start expressing your queries in English and just "translate" them to SQL after a while. (Or maybe I just write way, way too much SQL and I'm damaged now).
A join with grouping and aggregation
Using a join and GROUP BY is in my view the clearest and simplest way to express it. You say "here's the relationship between these two tables, now for each p.ids get me the max(c.timestamp)".
SELECT
p.ids,
max(c.timestamp)
FROM parents
LEFT OUTER JOIN children c ON (p.ids = c.parent_id)
WHERE p.name ILIKE '%something%'
GROUP BY p.ids;
I used a LEFT OUTER JOIN because, in your simple FOR loop, you'd get a result with a parent_id and null max if there were no matching rows. This preserves the same behaviour. If you want no row at all when there are no child rows, use an inner join.
A correlated subquery
SELECT
p.ids,
(SELECT max(timestamp) FROM children c WHERE c.parent_id = p.ids)
FROM parents
WHERE p.name ILIKE '%something%';
This approach is limited to cases where you only want one field from the associated child table unless you start doing horrible things with composite records. It'll generally result in the same query plan as the join approach, but it's less flexible.
It's closer to the "for loop" approach, in that it's saying "for each parent row do this on the child table".
A FOR loop in PL/PgSQL
This is slowest and is clumsy, but almost literally what you wrote.
FOR id IN SELECT ids FROM parents WHERE name ilike '%something%' LOOP
RETURN QUERY SELECT parent_id, max(timestamp) FROM children WHERE parent_id = id;
END LOOP;
Yes, I copied your code almost verbatim. It looks like perfectly valid PL/PgSQL except that there's no destination for the results. In the form above you'd need to declare the procedure RETURNS TABLE(...).
This last one is PL/PgSQL so it's only valid in a function.
It's the closest to what you wrote, and the simplest when thinking procedurally, but it's actually slow and cumbersome.
