Deleting recursively in a function (ERROR: query has no destination for result data)

Question

I have this table of relationships (only id_padre and id_hijo are interesting):

    id | id_padre | id_hijo | cantidad | posicion 
----+----------+---------+----------+----------
  0 |          |       1 |        1 |        0
  1 |        1 |       2 |        1 |        0
  2 |        1 |       3 |        1 |        1
  3 |        3 |       4 |        1 |        0
  4 |        4 |       5 |      0.5 |        0
  5 |        4 |       6 |      0.5 |        1
  6 |        4 |       7 |       24 |        2
  7 |        4 |       8 |     0.11 |        3
  8 |        8 |       6 |     0.12 |        0
  9 |        8 |       9 |     0.05 |        1
 10 |        8 |      10 |      0.3 |        2
 11 |        8 |      11 |     0.02 |        3
 12 |        3 |      12 |      250 |        1
 13 |       12 |       5 |      0.8 |        0
 14 |       12 |       6 |      0.8 |        1
 15 |       12 |      13 |       26 |        2
 16 |       12 |       8 |     0.15 |        3

This table store the links between nodes (id_padre = parent node and id_hijo = child node). I'm trying to do a function for a recursive delete of rows where I begin with a particular row. After deleted, I check if there are more rows with id_hijo column with the same value I used to delete the first row. If there aren't rows with this condition, I'll must to delete all the rows where id_padre are equal id_hijo of the deleted row.

i.e.: If I begin to delete the row where id_padre=3 and id_hijo=4 then I delete this row:

id | id_padre | id_hijo | cantidad | posicion
----+----------+---------+----------+----------
3 |        3 |       4 |        1 |        0

and the table remains like that:

id | id_padre | id_hijo | cantidad | posicion 
    ----+----------+---------+----------+----------
      0 |          |       1 |        1 |        0
      1 |        1 |       2 |        1 |        0
      2 |        1 |       3 |        1 |        1     
      4 |        4 |       5 |      0.5 |        0
      5 |        4 |       6 |      0.5 |        1
      6 |        4 |       7 |       24 |        2
      7 |        4 |       8 |     0.11 |        3
      8 |        8 |       6 |     0.12 |        0
      9 |        8 |       9 |     0.05 |        1
     10 |        8 |      10 |      0.3 |        2
     11 |        8 |      11 |     0.02 |        3
     12 |        3 |      12 |      250 |        1
     13 |       12 |       5 |      0.8 |        0
     14 |       12 |       6 |      0.8 |        1
     15 |       12 |      13 |       26 |        2
     16 |       12 |       8 |     0.15 |        3

Because of there aren't any row with id_hijo = 4 I will delete the rows where id_padre = 4....and so on..recursively. (in this example the process end here)

I have try to do this function (this function calls itself):

CREATE OR REPLACE FUNCTION borrar(integer,integer) RETURNS VOID AS
$BODY$ 
DECLARE
 padre ALIAS FOR $1;
 hijo ALIAS FOR $2; 
 r copia_rel%rowtype;
BEGIN
  DELETE FROM copia_rel WHERE id_padre = padre AND id_hijo = hijo;
  IF NOT EXISTS (SELECT id_hijo FROM copia_rel WHERE id_hijo = hijo) THEN
    FOR r IN SELECT * FROM copia_rel WHERE id_padre = hijo LOOP
    RAISE NOTICE 'Selecciono: %,%',r.id_padre,r.id_hijo;--for debugging
    SELECT borrar(r.id_padre,r.id_hijo);    
    END LOOP;
  END IF;
END;
$BODY$
LANGUAGE plpgsql;

But I get this error: ERROR: query has no destination for result data

I know that there are specific recursive ways in postgresql wit CTE. I have used it for traverse my graph, but I don't know how could use it in this case.

Answer 1

The error is due to the SELECT used to call the function recursively. PostgreSQL wants to put the results somewhere but is not told where.

If you want to run a function and discard results use PERFORM instead of SELECT in PL/PgSQL functions.