Iterating through a "linked list" in a single SQL query?

Question

I have a table that looks basically like this:

id | redirectid | data

where redirectid is the identifier of another line. Basically, if a row is selected and it has redirectid, then the redirectid data should be used in that place. There may be several redirects until redirectid is NULL. In essence, these redirects form a linked list in a table. What I would like to know is to specify id, is it possible to create an sql query that will iterate over all possible redirects and return the identifier at the end of the "list"?

This uses Postgresql 8.3, and I would like to do everything in the sql query, if possible (and not repeat in my code).

Answer 1

Does postgresql support recursive queries that use WITH clauses? If so, something like this might work. (If you want a test answer, include some CREATE TABLE and INSERT statements in your question, as well as the results needed for sample data in INSERT.)

with Links(id,link,data) as (
  select
    id, redirectid, data
  from T
  where redirectid is null
  union all
  select
    id, redirectid, null
  from T
  where redirectid is not null
  union all
  select
    Links.id,
    T.redirectid,
    case when T.redirectid is null then T.data else null end
  from T
  join Links
  on Links.link = T.id
)
  select id, data
  from Links
  where data is not null;

Additional notes:

:( You can implement recursion yourself based on the WITH clause. I don't know the syntax of postgresql for sequential programming, so this is a bit pseudo:

Paste the result of this query into a new table called Links:

select
    id, redirectid as link, data, 0 as depth
  from T
  where redirectid is null
  union all
  select
    id, redirectid, null, 0
  from T
  where redirectid is not null

Also declare an integer :: depth and initialize it to zero. Then repeat the following until it no longer adds lines to the links. Then the links will contain your result.

  increment ::depth;
  insert into Links
  select
    Links.id,
    T.redirectid,
    case when T.redirectid is null then T.data else null end,
    depth + 1
  from T join Links
  on Links.link = T.id
  where depth = ::depth-1;
end;

, , . , .

, , - (, ).

Answer 2

, :

create function FindLastId (ID as integer) returns integer as $$
    declare newid integer;
    declare primaryid integer;
    declare continue boolean;
    begin
        set continue = true;
        set primaryid = $1;
        while (continue)
            select into newid redirectid from table where id = :primaryid;

            if newid is null then
                set continue = false;
            else
                set primaryid = :newid;
            end if;
        end loop;

        return primaryid;
    end;
    $$ language pgplsql;

Postgres, - . , :

select id, FindLastId(id) as EndId from table

:

id     redirectid    data
1          3          ab
2        null         cd
3          2          ef
4          1          gh
5        null         ij

:

id    EndId
1       2
2       2
3       2
4       2
5       5

, , .