Обсуждение: 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 >