Message-ID: <3D60F041.10003@math.tu-berlin.de>
Date: Mon, 19 Aug 2002 15:18:57 +0200
From: Fritz Lehmann-Grube <lehmannf@math.TU-Berlin.DE>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.0) Gecko/20020529
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: recursive function returning "setof"
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit


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