CODEWITHSUNDEEP
postgresqlfunctionrecursionplpgsql
Roscos
Roscos

Reputation: 3

PL/pgSQL function does not return custom (record) types as expected

My function's job is to extract XML nodes. The code is as follows:

CREATE TYPE xml_node_looper_record AS (
  allomany xml,
  i integer,
  actual_node text,
  nodi_parts text[]
);

CREATE OR REPLACE FUNCTION ds.xml_node_looper_rec(rec xml_node_looper_record)
  RETURNS SETOF xml_node_looper_record AS
$BODY$
DECLARE 
  nodes text[];
  field_val text;
  r xml_node_looper_record;
  n integer;
BEGIN
  nodes = xpath(rec.actual_node, rec.allomany);
  IF nodes[1] IS NOT NULL THEN 
    rec.i = rec.i + 1;
    FOR n IN 1..array_upper(nodes, 1) LOOP
      IF rec.i = array_upper(rec.nodi_parts, 1) THEN 
        field_val = trim(ARRAY[xpath(rec.actual_node || '/text()', rec.allomany)]::text, ' {}"');
        IF field_val IS NOT NULL AND field_val != '' THEN 
          RAISE NOTICE '% % % %', n, rec.actual_node, rec.i, field_val;
          RETURN NEXT (NULL::xml, rec.i, rec.actual_node, ARRAY[field_val]::text[]);
        END IF;
      END IF;
      SELECT ds.xml_node_looper_rec((rec.allomany, rec.i, rec.actual_node || '[' || n::text || ']' || rec.nodi_parts[rec.i + 1], rec.nodi_parts)) INTO r;
    END LOOP;
  END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE COST 100;

As you can see the function is recursive and the goal is to collect field values from multiple XML nodes where we have no information, how many nodes we have under a tag. (I have a version with return table, but that method is too slow.) I use my own defined custom type, and when I check the return values with RAISE NOTICE, I can see the result in pgAdmin on the Messages tab, but the RETURN NEXT command returns nothing, only an empty table.

The parameters of my type:

What is the problem?

Upvotes: 0

Views: 94

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45910

You don't propagate the result of nested calls. RETURN NEXT pushes a result to the stack related to the function call. But this stack is private - if caller doesn't fetch this stack, then the result is cleaned. Anyway - any function instance (the called function) has its own result stack. This stack is not shared.

The recursive table function in PL/pgSQL should to look like:

postgres=# CREATE OR REPLACE FUNCTION foo(level int) RETURNS SETOF int AS $$
BEGIN
  IF level > 5 THEN RETURN; END IF;
  RETURN NEXT level;
  --!! must to take result of nested call
  RETURN QUERY SELECT * FROM foo(level + 1); 
  RETURN;
END;
$$ LANGUAGE plpgsql;

postgres=# SELECT * FROM foo(1);
┌─────┐
│ foo │
╞═════╡
│   1 │
│   2 │
│   3 │
│   4 │
│   5 │
└─────┘
(5 rows)

Your code is a equivalent of code:

postgres=# CREATE OR REPLACE FUNCTION foo(level int) RETURNS SETOF int AS $$
BEGIN
  IF level > 5 THEN RETURN; END IF;
  RETURN NEXT level;
  -- error, only call of nested function, but returned table is lost
  PERFORM foo(level + 1);
  RETURN;
END;
$$ LANGUAGE plpgsql;

postgres=# SELECT * FROM foo(1);
┌─────┐
│ foo │
╞═════╡
│   1 │
└─────┘
(1 row)

Upvotes: 2

Related Questions