错误:查询的结构与函数结果类型不匹配，返回的类型文本与期望的类型几何体不匹配

Question

试图编写一个函数，该函数返回DEM和高程中直线插值点的几何图形，但出现此错误ERROR: structure of query does not match function result type.点末尾的查询应返回区间的起点、终点和插值点的几何形状。这是我的功能：

test_get_slope_profile
CREATE OR REPLACE FUNCTION test_get_slope_profile(ways_id bigint, interval_ float default 10, way_table TEXT DEFAULT 'ways')
RETURNS TABLE(p_geom geometry, elevation float)
LANGUAGE plpgsql
AS $function$
DECLARE 
way_geom geometry;
length_meters float;
length_degree NUMERIC;
translation_m_degree NUMERIC;
BEGIN

IF way_table = 'ways' THEN 
SELECT geom, length_m, ST_Length(geom) 
INTO way_geom, length_meters, length_degree
FROM ways
WHERE id = ways_id; 
ELSEIF way_table = 'ways_userinput' THEN 
SELECT geom, length_m, ST_Length(geom) 
INTO way_geom, length_meters, length_degree
FROM ways_userinput 
WHERE id = ways_id; 
END IF;

translation_m_degree = length_degree/length_meters;
DROP TABLE IF EXISTS dump_points;
IF length_meters > (2*interval_) THEN 
CREATE TEMP TABLE dump_points AS 
SELECT (ST_DUMP(ST_Lineinterpolatepoints(way_geom,interval_/length_meters))).geom AS geom;

ELSEIF length_meters > interval_ AND length_meters < (2*interval_) THEN 
CREATE TEMP TABLE dump_points AS 
SELECT ST_LineInterpolatePoint(way_geom,0.5) AS geom;
interval_ = length_meters/2;
ELSE
CREATE TEMP TABLE dump_points AS
SELECT NULL::geometry AS geom;
END IF;

RETURN query
WITH points AS 
(
SELECT ROW_NUMBER() OVER() cnt, geom, length_meters 
FROM (
SELECT st_startpoint(way_geom) AS geom
UNION ALL 
SELECT geom FROM dump_points
UNION ALL 
SELECT st_endpoint(way_geom) 
) x
)
SELECT 'geom', SUM(idw.val/(idw.distance/translation_m_degree))/SUM(1/(idw.distance/translation_m_degree))::real AS elev
FROM points p, get_idw_values(geom) idw
WHERE p.geom IS NOT NULL 
GROUP BY cnt 
ORDER BY cnt;

END;
$function$;
ERROR:  structure of query does not match function result type
DETAIL:  Returned type text does not match expected type geometry in column 1.
CONTEXT:  PL/pgSQL function test_get_slope_profile(bigint,double precision,text) line 38 at RETURN QUERY
SQL state: 42804

Answer 1

在RETURN query语句中，不要执行SELECT 'geom', SUM(...))::real AS elev，而是执行SELECT geom, SUM(...))::real AS elev

前者返回文本'geom'，而后者返回列内容，即几何