Hello all,
I'd like to create a recursive function returning a "setof".
See the following situation:
CREATE TABLE sections( is serial, data text, contained_in int NOT NULL REFERENCES sections(id)
DEFERRABLE
);
INSERT INTO sections
VALUES(0,'ROOTSECTION',0)
I have triggers, that prevent loops and so on, so these "sections" form a tree.
Now I want - for a given section - to define a function, that finds the *SETOF*
all "ancestor"sections up to the "ROOTSECTION". That would need something
recursive.
The problem is:
A SQL-Function cannot be recursive because it cannot call itself, and it can
perform no loops.
A PLPGSQL-Function cannot return sets.
I know I can do it using a temporary table, but I don't like that for various
reasons:
- I don't want complicated handling of the temp table's name in case of
simultaneous calls
- The webserver, that operates on the DB shouldn't have "CREATE TABLE" "DROP
TABLE" or "DELETE" permissions
- It's not the natural, at least no "clean" solution
Yes - I thought about returning a refcursor, but it seemed not to help. A cursor
can only reference one static query.
I've been reading the manuals for quite a time now - no way! but I'm sure I
can't be the first one with that kind of a problem, so ...?
Greetings Fritz
