0

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

2
  • Are you looking for a pl/pgsql function or for a recursive query? Commented Dec 11, 2022 at 15:50
  • It does not matter, i worked until now with a recursive query, but it only handles the new values, but i want to see the old ones too (for getting the point between 0 and 512). (I tried also to re-handle the old values with a function that takes all values, and returns them together with the new ones, but i think that is very huge overload) Commented Dec 11, 2022 at 16:17

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.