From: Fritz Lehmann-Grube <lehmannf@math.TU-Berlin.DE>
To: pgsql-sql@postgresql.org
Subject: recursive function returning "setof"
Date: Mon, 19 Aug 2002 15:18:57 +0200
Message-ID: <3D60F041.10003@math.tu-berlin.de> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lehmannf@math.TU-Berlin.DE
Subject: Re: recursive function returning "setof"
In-Reply-To: <3D60F041.10003@math.tu-berlin.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox