I created function like below:
CREATE TYPE points AS (
"gid" double precision
, "elevation" double precision
, "distance" double precision
, "x" double precision
, "y" double precision
);
CREATE OR REPLACE FUNCTION public.nd_test4()
RETURNS SETOF points AS $$
DECLARE
sql text;
rec points;
BEGIN
sql := 'select
"gid"
, "elev" as "elevation"
, st_distance(ST_MakePoint(1093147, 1905632) , "the_geom" ) as "distance"
, st_x("the_geom") as "x"
, st_y("the_geom")as "y"
from
"elevation-test"
where
st_within("the_geom" ,st_buffer(ST_MakePoint(1093147, 1905632), 15) )
order by distance limit 4';
FOR rec IN EXECUTE(sql) LOOP
RETURN NEXT rec;
END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;
And when I run the function like select nd_test4();
, I get a result with no filed names like below.
How can I get result with filed name like this:
gid | elevation | distance | x | y
----+-----------+----------+---------+-------
1 | 350.0 | 10 | 12345.1 | 12435
Call the function with:
SELECT * FROM nd_test4();
Also, your function definition is needlessly convoluted. Simplify to:
CREATE OR REPLACE FUNCTION public.nd_test4()
RETURNS SETOF points AS
$func$
BEGIN
RETURN QUERY
SELECT gid
,elev -- AS elevation
,st_distance(ST_MakePoint(1093147, 1905632) , the_geom ) -- AS distance
,st_x(the_geom) -- AS x
,st_y(the_geom) -- AS y
FROM "elevation-test"
WHERE st_within(the_geom, st_buffer(ST_MakePoint(1093147, 1905632), 15))
ORDER BY distance
LIMIT 4;
END
$func$ LANGUAGE plpgsql;
Or better yet, use a plain SQL function here:
CREATE OR REPLACE FUNCTION public.nd_test4()
RETURNS SETOF points AS
$func$
SELECT gid
,elev -- AS elevation
,st_distance(ST_MakePoint(1093147, 1905632) , the_geom ) -- AS distance
,st_x(the_geom) -- AS x
,st_y(the_geom) -- AS y
FROM "elevation-test"
WHERE st_within(the_geom, st_buffer(ST_MakePoint(1093147, 1905632), 15))
ORDER BY distance
LIMIT 4
$func$ LANGUAGE sql;
No need for dynamic SQL.
I also stripped the gratuitous double quotes. Not needed for legal, lower-case identifiers. Exception is "elevation-test"
. You shouldn't use an operator (-
) as part of a table name. That's just begging for trouble.
Aliases in the function body are replaced by column names of the composite type. They are only visible inside the function and therefore just documentation in your case.