Short answer first, you are super close. Try this instead,
UPDATE my_table
SET the_geom =
ST_Transform(
ST_Segmentize(
ST_MakeLine(
ST_Transform(my_table.the_geom, 953027),
ST_Transform(CDB_LatLng(48.432044, -71.060316), 953027)
),
100000
),
4326
)
This would update your table from being points, to being a line from the original point to the point at -71, 48. I used the CDB_LatLng helper function. You didn't need to As_Text function, because that just turns geometries into human readable text.
Now the longer is that each of these functions has a documentation page, you can find those here,
ST_Transform: http://postgis.net/docs/ST_Transform.html
ST_Segmentize: http://postgis.net/docs/ST_Segmentize.html
ST_MakeLine: http://postgis.net/docs/ST_MakeLine.html
You are basically reprojecting the reference map to make it so that the straightest line between points follows more closely a curved globe (actually in this case I think it is conic, but minor detail). Next, you segmentize so that you have waypoints all along that line that follow the shortest path across the curved map. Then, your reproject back to WGS84. If you just grabbed the start and end points in the curved world, when you reproject back it would still just be a straight line. By segmentizing you get the shortest line plus waypoints along that line that use the curved map. When you reproject back to WGS84 then, it will still use the same way points which will now appear curved.
Hope that makes some sense.
Best Answer