translate plpgsql recursive function back to pg8.1

Question

I have the following plpgsql function that does work great on pg 8.3 and above but I need to translate it back to a pg 8.1 database and I can't seam to get it right.

Any tips? I need to get rid of the "RETURN QUERY" as it was not yet introduced in 8.1...

CREATE OR REPLACE FUNCTION specie_children (specie_id INT, self BOOLEAN)
      RETURNS SETOF specie AS
    $BODY$
    DECLARE
      r specie%ROWTYPE;
    BEGIN
      IF self THEN
        RETURN QUERY SELECT * FROM specie WHERE specieid = specie_id;
      END IF;
      FOR r IN SELECT * FROM specie WHERE parent = specie_id
      LOOP
        RETURN NEXT r;
        RETURN QUERY SELECT * FROM specie_children(r.specieid, FALSE);
      END LOOP;
      RETURN;
    END
    $BODY$
    LANGUAGE 'plpgsql';

How do I translate this ?

Maybe with this: http://stackoverflow.com/questions/53108/is-it-possible-to-make-a-recursive-sql-query — Luxspes, Jul 24 '12 at 10:02
You are aware that 8.1 is dead and unsupported? And 8.3 will be soon. You should really be moving on to a current version. — , Jul 24 '12 at 10:32
Just do with `specie_children` what you do already with `specie`. You could even use the same variable, because the type must match anyway. — Erwin Brandstetter, Jul 24 '12 at 13:06
I am aware of the support of PG db's... Erwin what do you mean here? I don't quit get it. — user87400, Jul 24 '12 at 13:17
Use the same `LOOP` and `RETURN NEXT r` construct (nested). @maniek has spelled it out in his answer (he uses a separate record `r2` var for the loop), but it should really be obvious? (Edited, last part was wrong.) — Erwin Brandstetter, Jul 24 '12 at 13:25

score 1 · Accepted Answer · answered Jul 24 '12 at 10:06

1

RETURN QUERY SELECT * FROM specie_children(r.specieid, FALSE);

could be rewritten as

for r2 in select * from specie_children(r.specieid, FALSE)
loop
    return next r2
end loop

answered Jul 24 '12 at 10:06

maniek

7,087
2
20
43

I keep getting the following error: set-valued function called in context that cannot accept a set – user87400 Jul 24 '12 at 13:41
@user87400: You have to call your function the way I display it in my example, with `SELECT * FROM ...` – Erwin Brandstetter Jul 24 '12 at 13:48
WHOOPS, so thats was my problem after all ;-) thx for the help! – user87400 Jul 24 '12 at 14:03

score 0 · Answer 2 · answered Jul 24 '12 at 13:41

Quick demo. Basically @maniek already provided the answer.

Test table:

CREATE TEMP TABLE specie(specieid int, parent int);
INSERT INTO specie VALUES
 (1,0), (2,0), (3,0)
,(11,1), (12,1), (13,1)
,(111,11), (112,11), (113,11);

Rewritten function:

CREATE OR REPLACE FUNCTION specie_children (specie_id INT, self BOOLEAN)
  RETURNS SETOF specie AS
$BODY$
DECLARE
   r specie%ROWTYPE;
BEGIN
IF self THEN
   FOR r IN SELECT * FROM specie WHERE specieid = $1
   LOOP
      RETURN NEXT r;
   END LOOP;
END IF;

FOR r IN SELECT * FROM specie WHERE parent = $1
LOOP
   RETURN NEXT r;
   FOR r IN SELECT * FROM specie_children(r.specieid, FALSE)
   LOOP
      RETURN NEXT r;
   END LOOP;
END LOOP;

RETURN;
END;
$BODY$
LANGUAGE plpgsql;

Call:

SELECT * FROM specie_children (1, true);

Returns:

specieid | parent
---------+-------
1        | 0
11       | 1
111      | 11
112      | 11
113      | 11
12       | 1
13       | 1

translate plpgsql recursive function back to pg8.1

2 Answers2