PostGIS integer loop

Question

PostGIS is very new to me, and I'm having difficulty performing an operation that probably isn't too difficult. I have vector layers each filled with the outline of a landmass through time. I want to query the distance between these landmasses for every matching timestep. I can do this manually:

select a.gid from_gid, b.gid to_gid, st_shortestline(a.geom,b.geom), st_distance(a.geom,b.geom) as distance
from
(select * from gavdos
where year=3) as a,
(select * from crete
where year=3) as b;

The 'year' value ranges in integers from 0 to 21. It would be convenient to do this query using a FOR loop. I am however highly unsuccessful in doing so, the pgAdmin III SQL thing already presents an error when I say DECLARE i integer;, thus before I am even ready to try and create a loop.

My question is if someone could give an example how I can (1) set-up the loop and (2) how to get the full output (as I suppose I need to update a table at every step in the loop..).

Thanks in advance! :-)

Answer 1

I would recommend to do this with a JOIN instead of a loop:

SELECT 
      a.gid AS from_gid
    , b.gid AS to_gid
    , st_shortestline(a.geom,b.geom)
    , st_distance(a.geom,b.geom) AS distance
FROM
    gavdos AS a
LEFT JOIN
    crete AS b
ON 
    a.year = b.year

If you really need to have a loop and do advanced processing which cannot be accomplished with simple SQL you can have a look at PL/pgSQL