I would like to do some calculations with postgresql in a recursive / iterative way. Here is a very rudimentary example: There is a line and on the line are 2 points. I want to create new points in the middle of every pair of points before existing. (This is just an example, this does not really make sense, because we will get infinitely many points and got at the same point as existing ones)
CREATE TABLE IF NOT EXISTS public.points
(
pos real
)
TABLESPACE pg_default;
INSERT INTO points VALUES (0),(1024);
What i want to achieve is something like this:
| pos | step | Parent A | Parent B |
| -------- | -------- | -------- | -------- |
| 0 | 1 | -1 | -1 |
| 1024 | 1 | -1 | -1 |
| 512 | 2 | 0 | 1024 |
| 256 | 3 | 0 | 512 |
| 768 | 3 | 512 | 1024 |
...
Now I have two starting points. How to build a request to achieve such a result? If you have a solution without the step, it would be great also, but i think it can help me, to prevent doing calculations twice.
After a few tries and help from the article Recursive cumulative function - reuse resulting rows as input I found this solution. Is this the way to go, or do you have a better solution?
CREATE TYPE public.points_type AS
(
pos real,
step integer,
parenta real,
parentb real
);
CREATE TYPE public.points_type_input AS
(
pos real,
step integer
);
CREATE OR REPLACE FUNCTION public.points_calc(
_points points_type_input[])
RETURNS SETOF points_type
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000
AS $BODY$
declare
new_point points_type;
Begin
FOR new_point IN
SELECT (new_p).pos AS pos,
(new_p).stepnow +1 AS step,
(new_p).parentA AS parentA,
(new_p).parentB AS parentB
FROM (
SELECT ((p1.pos + p2.pos)/2)::real AS pos,
(array_agg(p1.pos) OVER (PARTITION BY 1)) AS oldpos,
(max(p1.step) OVER (PARTITION BY 1)) AS stepnow,
p1.pos AS parentA,
p2.pos AS parentB
FROM unnest(_points) p1
CROSS JOIN unnest(_points) p2
WHERE p1.pos < p2.pos
) new_p
WHERE not ARRAY[pos] <@ oldpos
LOOP
RETURN NEXT new_point;
END LOOP;
RETURN;
END
$BODY$;
This is the best solution i found. Is there a better way?
CREATE OR REPLACE FUNCTION points_v2(end_step integer)
RETURNS TABLE(pos real, step integer, parenta real, parentb real)
LANGUAGE plpgsql AS
$func$
DECLARE
t record;
a boolean;
cnt integer;
BEGIN
DROP TABLE IF EXISTS pg_temp.result2;
CREATE TEMP TABLE result2 (pos real, step integer, parenta real, parentb real) ON COMMIT DROP;
FOR t IN
TABLE points ORDER BY pos
LOOP
INSERT INTO result2(pos, step, parenta, parentb)
SELECT t.pos, 1 AS step, -1 AS parenta, -1 AS parentb;
END LOOP;
a = true;
cnt = 0;
WHILE a AND cnt<end_step
LOOP
a = false;
cnt = cnt + 1;
FOR t IN
SELECT (newpoints.result).pos,
(newpoints.result).step,
(newpoints.result).parenta,
(newpoints.result).parentb
FROM ( SELECT points_calc(array_agg(ROW(p.pos, p.step)::points_type_input)) AS result
FROM result2 p
) newpoints
LOOP
a = true;
INSERT INTO result2(pos, step, parenta, parentb)
SELECT t.pos, t.step, t.parenta, t.parentb;
END LOOP;
END LOOP;
RETURN QUERY
SELECT r.pos, r.step, r.parenta, r.parentb
FROM result2 r;
END
$func$;
SELECT * FROM points_v2(3);
Is there someone, who can help me? Here is a dbfiddle example.
Thank you very much, best regards, Ludwig Rahlff