Thomas Steinmaurer schrieb am 29.09.2010 um 22:19 (+0200):

> Hello,
>
> the Firebird 2.1 release notes state that CTE are available in PSQL, but
> I don't find a way to have a recursive CTE in a stored procedure which
> I'm able to iterate through in e.g. a FOR SELECT ...

Here's an example of a recursive CTE in a procedure:

CREATE PROCEDURE Fof ( uid INTEGER, recdepth SMALLINT )
RETURNS (
lvl SMALLINT,
fid INTEGER
)
AS
-- Bis zu welcher Tiefe will ich gehen?
DECLARE VARIABLE DEPTH SMALLINT DEFAULT 3;
BEGIN
IF ( recdepth < DEPTH ) THEN
DEPTH = recdepth;
-- Compound looping block processing an implicit cursor - Borrie S.578, 617-20
FOR -- FOR/INTO/DO: FOR <select> INTO <vars> DO <compound>
-- Common Table Expression (CTE)
-- Celko, Smarties, S.397-9
-- Firebird 2.1 Release Notes S.44ff
-- C:\Programme\Firebird\Firebird_2_1\doc\Firebird_v2.1.3.ReleaseNotes.pdf
-- http://msdn.microsoft.com/en-us/library/ms190766.aspx
WITH RECURSIVE Friends( lvl, fid )
AS (
SELECT 1, a FROM FS WHERE b = :uid
UNION ALL
SELECT 1, b FROM FS WHERE a = :uid

UNION ALL

SELECT FR.lvl + 1, FS2.a FROM FS AS FS2
INNER JOIN Friends AS FR ON FS2.b = FR.fid
WHERE FR.lvl < :DEPTH
AND FS2.a <> :uid -- sich selber ignorieren
UNION ALL
SELECT FR.lvl + 1, FS2.b FROM FS AS FS2
INNER JOIN Friends AS FR ON FS2.a = FR.fid
WHERE FR.lvl < :DEPTH
AND FS2.b <> :uid -- sich selber ignorieren
)
SELECT lvl, fid FROM Friends -- Ende CTE
INTO :lvl, :fid -- FOR/INTO/DO
DO
BEGIN
SUSPEND; -- delivers the row to the row cache
END
END ^
COMMIT ^

--
Michael Ludwig