recursive function returning "setof"
От | Fritz Lehmann-Grube |
---|---|
Тема | recursive function returning "setof" |
Дата | |
Msg-id | 3D60F041.10003@math.tu-berlin.de обсуждение исходный текст |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: