conways_life.sql

/* Conway's Life in pl/pgsql */

/* The 8 directions for finding neighbors of a cell */
create temp table directions(dx int, dy int);
insert into directions(dx, dy) values(-1, -1), (0, -1), (1, -1), (-1, 0), (1, 0), (-1, 1), (0, 1), (1, 1);

/* alive cells, one row per */
create temp table cells(x int, y int);

/* setup seed cells */
insert into cells(x, y)
values(2, 1), (2, 2), (2, 3); /* oscillator */

/* storage for next generation */
create temp table next(x int, y int);

do $$
    declare cell record; /* for logging below */
begin
  /* run each generation */
  for i in 1..10 loop
    /* this is the core logic */
    insert into next(x,y)
    select neighbors.x, neighbors.y
    from (
      select cells.x + directions.dx as x, cells.y + directions.dy as y
      from cells
      join directions on 1=1
    ) neighbors
    left join cells on neighbors.x = cells.x and neighbors.y = cells.y
    group by  neighbors.x, neighbors.y, cells.x
    having
      count(*) = 3 or /* survival or birth */
      (count(*) = 2 and cells.x is not null); /* survival */

    /* swap */
    delete from cells;
    insert into cells (x, y) select x, y from next;
    delete from next;

    /* print out each alive cell*/
    raise info 'generation %', i;
    for cell in select * from cells order by x, y loop
      raise info '%, %', cell.x, cell.y;
    end loop;

  end loop;
end
$$;