[NOVICE] LOOP Functions - where to start?

[James David Smith]

Hi all,

Thanks for the help a week or two ago with matching the time fields. I
managed to move on from that issue at last which was pleasing. I'm now
struggling with LOOP functions though. I've been having a look around the
net for some gentle introductions to them but haven't found any good ones
yet - could someone point me towards them if they exist? I don't like the
ones in the manual much.

Once I've learnt how loops work, I want to try and do something like the
below (simplified). Wrote in pseudo-code for now as I'm not sure how of the
format to write it properly and get it working. Essentially I want to split
a line into a number of equally defined and evenly spaced points. I
discovered the st_line_interpolate_point function, but I need to put that
inside a loop and store each record that it generates into a new table.
_____________
j = 0.2
i = 0.2
WHILE i < '1'
LOOP
SELECT ST_Line_Interpolate_Point(line, j)
INTO new_record_in_new_table
j = i+j
END LOOP
____________

Thanks for the communitys help as always.

James

[Merlin Moncure]

On Mon, Jul 30, 2012 at 11:11 AM, James David Smith

Post by James David Smith
Hi all,
Thanks for the help a week or two ago with matching the time fields. I
managed to move on from that issue at last which was pleasing. I'm now
struggling with LOOP functions though. I've been having a look around the
net for some gentle introductions to them but haven't found any good ones
yet - could someone point me towards them if they exist? I don't like the
ones in the manual much.
Once I've learnt how loops work, I want to try and do something like the
below (simplified). Wrote in pseudo-code for now as I'm not sure how of the
format to write it properly and get it working. Essentially I want to split
a line into a number of equally defined and evenly spaced points. I
discovered the st_line_interpolate_point function, but I need to put that
inside a loop and store each record that it generates into a new table.
_____________
j = 0.2
i = 0.2
WHILE i < '1'
LOOP
SELECT ST_Line_Interpolate_Point(line, j)
INTO new_record_in_new_table
j = i+j
END LOOP
____________
Thanks for the communitys help as always.

how about:

INSERT INTO new_table
SELECT ST_Line_Interpolate_Point(null, v::float / 10) from
generate_series(2, 10) v;

we're using generate_series to break out values -- it generates
integers so we're doing the to float math from the generated value --
and wrapping the entire query into a insert/select. if you must use
loops (maybe for fine grained error handling), you've almost got it.
see here: http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html.

specifically, i'd be using this construction (for lloops are for integers):
LOOP
-- some computations
EXIT WHEN count > 100;
END LOOP;

plpgsql btw can sometimes be used with the DO construct:

DO
$$
BEGIN
LOOP
-- some computations
EXIT WHEN count > 100;
END LOOP;
END;
$$ LANGUAGE PLPGSQL;

merlin

[James David Smith]

Hi Merlin,

Thank you so much for your help - I've managed to do it ! :-) I didn't
delve into LOOPS in the end, which worries me a little bit as I think that
I need to learn how to understand them, but in the meantime all is good. My
final query looks like the below.

Basically I have a load of GPS points. Some are every few seconds, some are
every minute etc. The time between each point is not regular. But for the
work I am doing I need a point for every second. So I first make a line
between all the points that exist, then I calculate the time between the
first point and the end point, and see how many seconds this is. Then I use
the number that has been generated from this inside the
st_line_interpolate_point function that you helped me with to split the
line I've just made into the correct number of points.

SELECT ST_Line_Interpolate_Point(
(SELECT St_MakeLine(the_geom_osgb36) as line
FROM (SELECT the_geom_osgb36
FROM gps_12_07_2012
WHERE person_id = '1'
ORDER BY date_time) a),
v::float /
(SELECT (EXTRACT(EPOCH FROM (MAX(date_time) -
MIN(date_time)))/1::integer)::integer FROM gps_12_07_2012 WHERE person_id =
'1')
) INTO new_table from
generate_series(1,
(SELECT (EXTRACT(EPOCH FROM (MAX(date_time) -
MIN(date_time)))/1::integer)::integer FROM gps_12_07_2012 WHERE person_id =
'1')
) v;

I think I will read the manual now so that I can understand how to store
this as a function because I will use this alot in the work I'm doing.

Best wishes

James

Post by Merlin Moncure
On Mon, Jul 30, 2012 at 11:11 AM, James David Smith

Post by James David Smith
Hi all,
Thanks for the help a week or two ago with matching the time fields. I
managed to move on from that issue at last which was pleasing. I'm now
struggling with LOOP functions though. I've been having a look around the
net for some gentle introductions to them but haven't found any good ones
yet - could someone point me towards them if they exist? I don't like the
ones in the manual much.
Once I've learnt how loops work, I want to try and do something like the
below (simplified). Wrote in pseudo-code for now as I'm not sure how of

the

Post by James David Smith
format to write it properly and get it working. Essentially I want to

split

Post by James David Smith
a line into a number of equally defined and evenly spaced points. I
discovered the st_line_interpolate_point function, but I need to put that
inside a loop and store each record that it generates into a new table.
_____________
j = 0.2
i = 0.2
WHILE i < '1'
LOOP
SELECT ST_Line_Interpolate_Point(line, j)
INTO new_record_in_new_table
j = i+j
END LOOP
____________
Thanks for the communitys help as always.

INSERT INTO new_table
SELECT ST_Line_Interpolate_Point(null, v::float / 10) from
generate_series(2, 10) v;
we're using generate_series to break out values -- it generates
integers so we're doing the to float math from the generated value --
and wrapping the entire query into a insert/select. if you must use
loops (maybe for fine grained error handling), you've almost got it.
http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html.
LOOP
-- some computations
EXIT WHEN count > 100;
END LOOP;
DO
$$
BEGIN
LOOP
-- some computations
EXIT WHEN count > 100;
END LOOP;
END;
$$ LANGUAGE PLPGSQL;
merlin