Re: LOOP Functions - where to start?

On Mon, Jul 30, 2012 at 11:11 AM, James David Smith
<james(dot)david(dot)smith(at)gmail(dot)com> wrote:
> 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