postgresql de recursive

From: Ali Çelik <ali(at)alicelik(dot)com(dot)tr>
To: pgsql-tr-genel <pgsql-tr-genel(at)postgresql(dot)org>
Subject: postgresql de recursive
Date: 2012-01-02 09:36:29
Message-ID: CADUN1A+RppcoU=jgLQpTZjOhYYGvPJRPVikX6LYK-p6DJ66epA@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-tr-genel

Merhaba RECURSIVE i fonksiyon içinde kullanmaya çalıştım fakat FOR ifadesi
ile beraber kullanamıyorum. Yardımcı olabilecek var mı acaba

///////////////////////////////////////tablo
CREATE TABLE public.persons (
id INTEGER,
name TEXT,
master_id INTEGER,
commission_percent NUMERIC(15,2) DEFAULT 10 NOT NULL,
CONSTRAINT persons_id_master_id_key UNIQUE(id, master_id)
) WITHOUT OIDS;
//////////////////////////

////////////////////////////////////////////////////////////// çalışan
fonksiyon //////////////////////////////////////////////////////////////
CREATE OR REPLACE FUNCTION public.function1 (
)
RETURNS TABLE (
nnode integer,
ppath integer []
) AS
$body$
DECLARE
_table1 record;
BEGIN
WITH RECURSIVE rtable(node, path) AS (
SELECT id, ARRAY[id] FROM persons WHERE master_id IS NULL
UNION ALL
SELECT p1.id, rtable.path || ARRAY[p1.id] FROM persons p1 JOIN rtable ON
(p1.master_id = rtable.node)
WHERE id != any (rtable.path)
)
SELECT * into _table1 FROM rtable ORDER BY path;
nnode:=_table1.node;ppath:=_table1.path;
RETURN NEXT ;
END;
//////////////////////////////////////////////////////////// sadece 1 kayıt
döndürdüğü için uygun değil
///////////////////////////////////////////////////////////////

////////////////////////////////////////////////////////////// hata veren
fonksiyon //////////////////////////////////////////////////////////////
CREATE OR REPLACE FUNCTION public.function1 (
)
RETURNS TABLE (
nnode integer,
ppath integer []
) AS
$body$
DECLARE
_table1 record;
BEGIN
WITH RECURSIVE rtable(node, path) AS (
SELECT id, ARRAY[id] FROM persons WHERE master_id IS NULL
UNION ALL
SELECT p1.id, rtable.path || ARRAY[p1.id] FROM persons p1 JOIN rtable ON
(p1.master_id = rtable.node)
WHERE id != any (rtable.path)
)

FOR _table1 IN (SELECT * into _table1 FROM rtable ORDER BY path) LOOP

nnode:=_table1.node;ppath:=_table1.path;

RETURN NEXT ;
END LOOP;
END;

//////////////////////////////////////////////////////// for kullanmama
izin vermiyor
///////////////////////////////////////////////////////////////////

Ali ÇELİK

Responses

Browse pgsql-tr-genel by date

  From Date Subject
Next Message N. Can KIRIK 2012-01-02 12:23:10 Re: postgresql de recursive
Previous Message Devrim GÜNDÜZ 2011-12-27 15:07:34 İstanbul'da 1 günlük PostgreSQL eğitimi