www_fdw_server_gpx.sql

DROP EXTENSION IF EXISTS www_fdw CASCADE;                             
CREATE EXTENSION www_fdw;                                             
CREATE SERVER www_fdw_server_gpx 
FOREIGN DATA WRAPPER www_fdw
    OPTIONS (   uri 'http://thinkspatial.com.au/xml/2012-05-02.gpx',
        response_type 'xml', 
        response_deserialize_callback 'gpx_deserialize_callback');

CREATE USER MAPPING FOR current_user SERVER www_fdw_server_gpx;      
CREATE FOREIGN TABLE www_fdw_server_gpx (
    "id"    integer,
    "lat"   numeric,
    "lng"   numeric,
    "ele"   numeric,
    "time"  timestamp,
    "speed" numeric
) SERVER www_fdw_server_gpx; 

CREATE OR REPLACE FUNCTION gpx_deserialize_callback(options WWWFdwOptions, response text)
    RETURNS SETOF www_fdw_server_gpx AS $$
DECLARE  
    rows  RECORD;
    stime timestamp;
    lat   numeric;
    lng   numeric;
    ele   numeric;
    speed numeric;
    count integer := 0;
    r RECORD;
BEGIN
    FOR rows IN SELECT unnest(xpath('//gpx:trkpt', response::xml, ARRAY[ARRAY['gpx', 'http://www.topografix.com/GPX/1/1']])) 
    LOOP 
        count := count+1;
        ele   := CAST(CAST((xpath('/trkpt/ele/text()', rows.unnest))[1] As varchar) As numeric);
        lng   := CAST(CAST((xpath('/trkpt/@lon', rows.unnest))[1] As varchar) As numeric);
        lat   := CAST(CAST((xpath('/trkpt/@lat', rows.unnest))[1] As varchar) As numeric);
        speed := CAST(CAST((xpath('/trkpt/speed/text()', rows.unnest))[1] As varchar) As numeric);
        stime := TO_TIMESTAMP(CAST((xpath('/trkpt/time/text()', rows.unnest))[1] As varchar), 'YYYY-MM-DD HH24:MI:SS ');
        r := ROW(count,lat, lng, ele, stime, speed);
        RETURN NEXT r;
    END LOOP;
END; $$ LANGUAGE PLPGSQL;