Why my query returns record type instead of original data type in postgresql?

Question

I am new to PostGIS and PostgreSQL. I am trying to find out a column of geometry type data and want to makeline or want the text format of the data. So, I used the following queries:

select st_makeline(the_geom) 
from (
  SELECT edge.start_geom 
  FROM public.edge_table edge 
  INNER JOIN routing_result route ON edge.start_id = route.node
  UNION 
  SELECT edge.end_geom 
  FROM public.edge_table edge 
  INNER JOIN routing_result route ON edge.end_id = route.node
) as the_geom 

and

select st_astext(the_geom) 
from (
  SELECT edge.start_geom 
  FROM public.edge_table edge 
  INNER JOIN routing_result route ON edge.start_id = route.node
  UNION 
  SELECT edge.end_geom 
  FROM public.edge_table edge 
  INNER JOIN routing_result route ON edge.end_id = route.node
) as the_geom

But both the queries are returning following error:

ERROR:  function st_makeline(record) does not exist

and

ERROR:  function st_astext(record) does not exist

And if I run without st_makeline or st_astext, i.e. the following:

SELECT edge.start_geom 
FROM public.edge_table edge 
INNER JOIN routing_result route ON edge.start_id = route.node
UNION 
SELECT edge.end_geom 
FROM public.edge_table edge 
INNER JOIN routing_result route ON edge.end_id = route.node

Then it is executing perfectly and returning the geometry values as "record" type instead of geometry type. So, I understood that the above query is not returning the geometry type so st_astext or st_makeline is not executing. But why is this happening and how to fix it?

Answer 1

You perform st_makeline/st_astext on the whole subquery which you named the_geom. (the result of a subquery is of type record).

But you just want one column of your record, so you should name the column the_geom and the subquery something else.

This should work:

select st_makeline(the_geom) 
from (
  SELECT edge.start_geom as the_geom 
  FROM public.edge_table edge 
  INNER JOIN routing_result route ON edge.start_id = route.node
  UNION 
  SELECT edge.end_geom as the_geom 
  FROM public.edge_table edge 
  INNER JOIN routing_result route ON edge.end_id = route.node
) as subquery