Thread: SQL equivalent to nested loop

SQL equivalent to nested loop

From

Markus Bertheau ☭

Date:

06 June 2005, 16:52:48

Hi,

I basically need the SQL equivalent of the following pseudo code:

BEGIN
FOR v IN SELECT * FROM f(4, 'foo') LOOP   FOR w IN SELECT * FROM f(v.id, 'bar') LOOP       RETURN NEXT W   END LOOP;
END LOOP;
RETURN;

Is that possible in SQL?

Markus

-- 
Markus Bertheau ☭ <twanger@bluetwanger.de>

Re: SQL equivalent to nested loop

From

KÖPFERL Robert

Date:

06 June 2005, 18:08:21

This is basicly a Join, a cross table

i.e.

select b.w from  table a, table b where ...



|-----Original Message-----
|From: twanger@bluetwanger.de [mailto:twanger@bluetwanger.de]
|Sent: Montag, 06. Juni 2005 18:53
|To: pgsql-sql@postgresql.org
|Subject: [SQL] SQL equivalent to nested loop
|
|
|Hi,
|
|I basically need the SQL equivalent of the following pseudo code:
|
|BEGIN
|FOR v IN SELECT * FROM f(4, 'foo') LOOP
|    FOR w IN SELECT * FROM f(v.id, 'bar') LOOP
|        RETURN NEXT W
|    END LOOP;
|END LOOP;
|RETURN;
|
|Is that possible in SQL?
|
|Markus
|
|--
|Markus Bertheau ☭ <twanger@bluetwanger.de>
|
|
|---------------------------(end of
|broadcast)---------------------------
|TIP 7: don't forget to increase your free space map settings
|

Re: SQL equivalent to nested loop

From

Markus Bertheau

Date:

06 June 2005, 23:17:04

Dnia 06-06-2005, pon o godzinie 20:08 +0200, KÖPFERL Robert napisał(a):
> This is basicly a Join, a cross table
>
> i.e.
>
> select b.w from  table a, table b where ...

You'd hope that. The problem is that you don't have the tables and
afterwards specify the join condition, but in specifying the tables
(=calling the function) you have the join condition already. I can't
figure out the right syntax. What are you proposing?

SELECT * from f(4, 'foo') as b, f(b.int, 'bar')?

oocms=# select * from object_get_list_of_reference_property(311,
'Themen') as b, object_get_list_of_reference_property(b.object_id,
'Objekte');
ERROR:  ?????????????? ????????? ??
FROM ?? ????? ????????? ?? ?????? ????????? ?? ??? ?? ?????? ???????

I can't get an english error message atm :/, but that doesn't work.

Markus

--
Markus Bertheau <twanger@bluetwanger.de>

Re: SQL equivalent to nested loop

From

KÖPFERL Robert

Date:

07 June 2005, 08:59:06

Instead of tablenames just use functions (tablefunctions)

like my example
select a."Id", a."Code" from "GetLanguages"() a,  "GetLanguages"() b
"2","en"
"2","en"
"1","de"
"1","de"

|-----Original Message-----
|From: Markus Bertheau [mailto:twanger@bluetwanger.de]
|Sent: Dienstag, 07. Juni 2005 01:17
|To: KÖPFERL Robert
|Cc: pgsql-sql@postgresql.org
|Subject: RE: [SQL] SQL equivalent to nested loop
|
|
|Dnia 06-06-2005, pon o godzinie 20:08 +0200, KÖPFERL Robert napisał(a):
|> This is basicly a Join, a cross table
|>
|> i.e.
|>
|> select b.w from  table a, table b where ...
|
|You'd hope that. The problem is that you don't have the tables and
|afterwards specify the join condition, but in specifying the tables
|(=calling the function) you have the join condition already. I can't
|figure out the right syntax. What are you proposing?
|
|SELECT * from f(4, 'foo') as b, f(b.int, 'bar')?
|
|oocms=# select * from object_get_list_of_reference_property(311,
|'Themen') as b, object_get_list_of_reference_property(b.object_id,
|'Objekte');
|ERROR:  ?????????????? ????????? ??
|FROM ?? ????? ????????? ?? ?????? ????????? ?? ??? ?? ?????? ???????
|
|I can't get an english error message atm :/, but that doesn't work.
|
|Markus
|
|--
|Markus Bertheau <twanger@bluetwanger.de>
|