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
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 |