Postgre start with connect by prior

[ GORSHTAK @ 16.08.2012. 17:16 ] @

Koji je najefikasniji/najjednostavniji/najbolji ekvivalent oraclovom upitu:

Code (sql):

SELECT id, parent, opis

  FROM tabela

START WITH id = 1

CONNECT BY prior id = parent;

[ michaelk @ 23.08.2012. 14:23 ] @

To je neki rekurzivni upit?

nesto kao ovo (CTE) :

Code:

WITH RECURSIVE subtabela AS
(
    SELECT id, parent, opis FROM tabela WHERE id = 1

    UNION ALL

    SELECT t.id, t.parent, t.opis FROM tabela t
               INNER JOIN subtabela st ON t.parent = st.id
)
SELECT * FROM subtabela ;

[ aca andrijevic @ 23.08.2012. 15:09 ] @

Code:

CREATE OR REPLACE FUNCTION connectby(text, text, text, text, text, integer)
  RETURNS SETOF record AS
'$libdir/tablefunc', 'connectby_text_serial'
  LANGUAGE 'c' STABLE STRICT
  COST 1
  ROWS 1000;
ALTER FUNCTION connectby(text, text, text, text, text, integer) OWNER TO postgres;

Ne znam koju verziju PG koristis, ako je manja od 8.2 najverovatnije ce ti javljati gresku
na COST i ROWS, mozes da izbacis to.

Pozivas ovako:

Code:

SELECT ta.id,ta.parrent,ta.opis FROM tabela ta,
CONNECTBY('tabela', 'id','parrent', 'opis', '0', 0) as t(ide int, p_id int, lv int, pos int)
WHERE ta.id = ide