Crr4vYmd

1DROP TABLE IF EXISTS mytable;
2CREATE TABLE mytable (
3 pk SERIAL PRIMARY KEY,
4 name TEXT,
5 size DOUBLE PRECISION,
6 geom GEOMETRY
7 );
8
9INSERT INTO mytable (name, size, geom) VALUES
10  ('Peter', 1.0, 'POINT(2 34)'),
11  ('Paul', 1.0, 'POINT(2 34)');
12
13DROP TABLE IF EXISTS mytable_2;
14CREATE TABLE mytable_2 (
15 pk SERIAL PRIMARY KEY,
16 name TEXT,
17 size DOUBLE PRECISION,
18 geom GEOMETRY
19 );
20
21INSERT INTO mytable_2 (name, size, geom) VALUES
22  ('Peter', 1.0, 'POINT(2 34)'),
23  ('Paul', 1.0, 'POINT(2 34)');
24
25
26CREATE OR REPLACE FUNCTION row_to_geojson(r RECORD, geom_column TEXT DEFAULT 'geom')
27RETURNS TEXT AS 
28$$
29DECLARE 
30 json_props jsonb;
31 json_geom jsonb;
32 json_type jsonb;
33BEGIN
34 json_props := row_to_json(r);
35 IF NOT json_props ? geom_column THEN
36   RAISE EXCEPTION 'geometry column ''%'' is missing', geom_column;
37 END IF;
38 json_geom := ST_AsGeoJSON((json_props ->> geom_column)::geometry)::jsonb;
39 json_geom := jsonb_build_object('geometry', json_geom);
40 json_props := jsonb_build_object('properties', json_props - geom_column);
41 json_type := jsonb_build_object('type', 'Feature');
42 return (json_type || json_geom || json_props)::text;
43END; 
44$$ 
45LANGUAGE 'plpgsql' VOLATILE;
46
47SELECT row_to_Geojson(t.*, 'geom') FROM mytable t;                                                                           
48
49SELECT row_to_Geojson(t.*, 'geom') FROM mytable_2 t;