

-- create a small table Foo with some stuff
DROP TABLE Foo;
CREATE TABLE Foo(fid SERIAL, stuff TEXT);
INSERT INTO Foo(fid,stuff) VALUES
  (1, 'one'),
  (2, 'two');
;

-- CAUTION: side effect on Foo! That is the point!
CREATE OR REPLACE FUNCTION updateFoo(id INTEGER, st TEXT) RETURNS TEXT
VOLATILE STRICT AS $$
  UPDATE Foo SET stuff=st WHERE fid=id;
  SELECT ARRAY(SELECT fid || ':' || stuff FROM Foo ORDER BY fid)::TEXT;
$$ LANGUAGE SQL;

-- let us iterate...
WITH RECURSIVE t(id,fid,st,dt) AS (
  SELECT
    0, 0, '', ARRAY(SELECT fid || ':' || stuff FROM Foo ORDER BY fid)::TEXT
  UNION
  SELECT
    t.id+1, f.fid, f.stuff,
      -- side effect on Foo expected
      updateFoo(2-id, '*')
  FROM t
  -- bug: Foo updates from previous iterations are not seen from here
  JOIN Foo AS f ON (t.id+1=f.fid)
)
SELECT * FROM t;


-- more or less expected behavior:

-- create a small table Foo with some stuff
TRUNCATE Foo;
INSERT INTO Foo(fid,stuff) VALUES
  (1, 'one'),
  (2, 'two');
;

DROP TABLE t;
CREATE TABLE t(
 id INTEGER,
 fid INTEGER,
 st TEXT,
 dt TEXT
);

-- 0
INSERT INTO t(id,fid,st,dt)
  VALUES(0, 0, '', '');

-- 1
INSERT INTO t(id,fid,st,dt)
  SELECT
    t.id+1, f.fid, f.stuff,
      -- side effect on Foo expected
      updateFoo(2-id, '*')
  FROM t
  -- bug: Foo updates are not seen here
  JOIN Foo AS f ON (t.id+1=f.fid)
  WHERE id=0;

-- 2
INSERT INTO t(id,fid,st,dt)
  SELECT
    t.id+1, f.fid, f.stuff,
      -- side effect on Foo expected
      updateFoo(2-id, '*')
  FROM t
  -- bug: Foo updates are not seen here
  JOIN Foo AS f ON (t.id+1=f.fid)
  WHERE id=1;

SELECT * FROM t;
