What you have probably isn't as bad as you think it is.
Why not something along the lines of
with recursive pointNums (n) as (
select 1
union all
select n+1 from pointNums
where n < (select NumPoints(exteriorring(shape)) from tmp)
)
select
n,
X(PointN(exteriorring(shape), n)) as Longitude,
Y(PointN(exteriorring(shape), n)) as Latitude
from
tmp inner join pointNums;
If you have the generate_series extension I think it'd be the equivalent of
select
value,
X(PointN(exteriorring(shape), value)) as Longitude,
Y(PointN(exteriorring(shape), value)) as Latitude
from
tmp, generate_series(1, (SELECT NumPoints(exteriorring(shape)) from tmp), 1);
...but I have been known to be wrong. This is all assuming a single record in tmp, but could probably be modified for a tmp table with multiple entries.