Thread: recursive function returning "setof"

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

> 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.

It can perform loops.  Check the manual- you can do FOR and WHILE loops.
7.3 will be able to return sets from PLPGSQL funcs it seems.

Chris

Thanx Chris,

but I can't find it.
My Programmer's Guide (.../pgsql/doc/html/xfunc-sql.html) says
  "12.2. Query Language (SQL) Functions   SQL functions execute an arbitrary list of SQL statements,"

Nothing about control structures in SQL functions additional to the normal 
command-line syntax. Where can I find it ?

and (.../pgsql/doc/html/plpgsql.html#PLPGSQL-ADVANTAGES)
  "23.1.1.1. Better Performance  SQL is the language PostgreSQL (and most other Relational Databases) use as
querylanguage. It's portable and easy to learn. But every SQL statement 
 
must be   executed individually by the database server.

That means that your client application must send each query to the database 
server, wait for it to process it, receive the results, do some computation, 
then send other queries to the server."

Isn't it just the thing in plpgsql to add control structures to SQL ?

Fritz

Christopher Kings-Lynne wrote:
>>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.
> 
> 
> It can perform loops.  Check the manual- you can do FOR and WHILE loops.
> 7.3 will be able to return sets from PLPGSQL funcs it seems.
> 
> Chris
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

http://www3.us.postgresql.org/users-lounge/docs/7.2/postgres/plpgsql-control
-structures.html

Chris

----- Original Message -----
From: "Fritz Lehmann-Grube" <lehmannf@math.TU-Berlin.DE>
To: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Cc: <pgsql-sql@postgresql.org>
Sent: Monday, August 19, 2002 11:22 PM
Subject: Re: [SQL] recursive function returning "setof"


>
> Thanx Chris,
>
> but I can't find it.
> My Programmer's Guide (.../pgsql/doc/html/xfunc-sql.html) says
>
>    "12.2. Query Language (SQL) Functions
>     SQL functions execute an arbitrary list of SQL statements,"
>
> Nothing about control structures in SQL functions additional to the normal
> command-line syntax. Where can I find it ?
>
> and (.../pgsql/doc/html/plpgsql.html#PLPGSQL-ADVANTAGES)
>
>    "23.1.1.1. Better Performance
>    SQL is the language PostgreSQL (and most other Relational Databases)
use as
>         query language. It's portable and easy to learn. But every SQL
statement
> must be   executed individually by the database server.
>
> That means that your client application must send each query to the
database
> server, wait for it to process it, receive the results, do some
computation,
> then send other queries to the server."
>
> Isn't it just the thing in plpgsql to add control structures to SQL ?
>
> Fritz
>
> Christopher Kings-Lynne wrote:
> >>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.
> >
> >
> > It can perform loops.  Check the manual- you can do FOR and WHILE loops.
> > 7.3 will be able to return sets from PLPGSQL funcs it seems.
> >
> > Chris
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>