Re: postgresql de recursive

selam,

yanılmıyorsma, CTE sorgusunu bir view olarak kaydedersen fonksiyon içinde
rahat bir şekilde kullanabilirsin.

2012/1/2 Ali Çelik <ali(at)alicelik(dot)com(dot)tr>

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