Hi there,
Having some problems today. Hopefully someone can help. The situation is...
I have a query that takes data from one of my tables, sends it to an
API, gets some data back, and then writes it to a results table.
The data comes from a table called 'stage_cleaned'
The query is called 'create_route_geometry_mapquest'
The results table is called 'route_geom'
The problem that I have been having is that if I send say 50 requests
to the API, and all but the 49th are completed fine, none of my
results are wrote to the results table. It's all or nothing. To fix
this I've tried to put the function within a loop and identified when
the errors occur and put them as an exception. It now works fine and
the functions always goes through the entire data. However it only
write the data to the results table at the end. Is this normal? Could
it not write the data to the results table after it's made each
request? Thoughts? Query below:
------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION routing_loop() RETURNS VOID AS $$
DECLARE
record_number RECORD;
BEGIN
FOR record_number IN SELECT id FROM stage_cleaned WHERE google_mode =
'walking' AND route_geom IS NULL ORDER BY id LIMIT 5
LOOP
LOOP
BEGIN
PERFORM create_route_geometry_mapquest(
google_origin::text,
google_destination::text,
google_mode::text,
id::text
),
Notice('did stage cleaned id number ' || id ),
pg_sleep(1)
FROM stage_cleaned
WHERE route_geom IS NULL
AND google_mode = 'walking'
AND id = record_number.id
ORDER BY id;
EXIT;
EXCEPTION
WHEN SQLSTATE 'XX000' THEN
END;
END LOOP;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
----------------------------------------------------------------------------
Thanks
James
