pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedrecursive function returning "setof"
4+ messages / 2 participants
[nested] [flat]
* recursive function returning "setof"
@ 2002-08-19 13:18 Fritz Lehmann-Grube <lehmannf@math.TU-Berlin.DE>
2002-08-19 14:52 ` Re: recursive function returning "setof" Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 4+ messages in thread
From: Fritz Lehmann-Grube @ 2002-08-19 13:18 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: recursive function returning "setof"
2002-08-19 13:18 recursive function returning "setof" Fritz Lehmann-Grube <lehmannf@math.TU-Berlin.DE>
@ 2002-08-19 14:52 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-19 15:22 ` Re: recursive function returning "setof" Fritz Lehmann-Grube <lehmannf@math.TU-Berlin.DE>
0 siblings, 1 reply; 4+ messages in thread
From: Christopher Kings-Lynne @ 2002-08-19 14:52 UTC (permalink / raw)
To: Fritz Lehmann-Grube <lehmannf@math.TU-Berlin.DE>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: recursive function returning "setof"
2002-08-19 13:18 recursive function returning "setof" Fritz Lehmann-Grube <lehmannf@math.TU-Berlin.DE>
2002-08-19 14:52 ` Re: recursive function returning "setof" Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2002-08-19 15:22 ` Fritz Lehmann-Grube <lehmannf@math.TU-Berlin.DE>
2002-08-19 15:52 ` Re: recursive function returning "setof" Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 4+ messages in thread
From: Fritz Lehmann-Grube @ 2002-08-19 15:22 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: recursive function returning "setof"
2002-08-19 13:18 recursive function returning "setof" Fritz Lehmann-Grube <lehmannf@math.TU-Berlin.DE>
2002-08-19 14:52 ` Re: recursive function returning "setof" Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-19 15:22 ` Re: recursive function returning "setof" Fritz Lehmann-Grube <lehmannf@math.TU-Berlin.DE>
@ 2002-08-19 15:52 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 0 replies; 4+ messages in thread
From: Christopher Kings-Lynne @ 2002-08-19 15:52 UTC (permalink / raw)
To: Fritz Lehmann-Grube <lehmannf@math.TU-Berlin.DE>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-08-19 15:52 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-19 13:18 recursive function returning "setof" Fritz Lehmann-Grube <lehmannf@math.TU-Berlin.DE>
2002-08-19 14:52 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-19 15:22 ` Fritz Lehmann-Grube <lehmannf@math.TU-Berlin.DE>
2002-08-19 15:52 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
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