Re: PL/PGSQL -- How To Return a Temp Table
От | Pavel Stehule |
---|---|
Тема | Re: PL/PGSQL -- How To Return a Temp Table |
Дата | |
Msg-id | Pine.LNX.4.44.0306231243080.17572-100000@kix.fsv.cvut.cz обсуждение исходный текст |
Ответ на | PL/PGSQL -- How To Return a Temp Table (Harry Yau <harry@aurasound.com.hk>) |
Список | pgsql-general |
Hello You can return table (without using temp table). CREATE TYPE tf AS (f1 varchar(10), f2 varchar(10)); CREATE OR REPLACE FUNCTION makesettf(integer) RETURNS SETOF tf AS ' DECLARE f tf%ROWTYPE; BEGIN FOR i IN 1..$1 LOOP f.f1 := CAST(i AS varchar(10)); f.f2 := ''bbbbb ''||CAST(i AS varchar(10)); RAISE NOTICE ''%'', f.f1; RETURN NEXT f; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; SELECT a.*, b.* FROM makesettf(10) a JOIN makesettf(5) b ON a.f1 = b.f1; SELECT * FROM makesettf(3) UNION ALL SELECT * FROM makesettf(8); ps On Mon, 23 Jun 2003, Harry Yau wrote: > Hi All, > I tried to create a function that will gether info around the database > and put those info into a temp table that is created inside the > function. At the end, of the function, it return all the content of the > temp table then the temp table should be droped automatically. > I am wondering what return type I should use. Should it be ' refcursor > of the temp table?'. However, the temp table is create inside the > function. how can I use it as the return type. Moreover, the temp table > is droped when the function terminiated. How could I use the result to > de-refer ther cursor??........ > > Then I tried to create a type like > > CREATE TYPE TempTableHolder AS ( aa text, bb text); > > The structure of the type TempTableHolder will be exactly the same as > that of the temp table. Then I create the function like: > > create function TestFun(Varchar) returns setof TempTableHolder > as > ' > declare > InF1 ALIAS FOR $1; > r_TempTableHolder TempTableHolder%rowtype; > r_Table1 Table1%rowtype; > r_Table2 Table2%rowtype; > begin > FOR r_Table1 IN EXECUTE ''SELECT * FROM Table1 where F1 = > ''||InF1||'' '' LOOP > FOR r_Table2 IN EXECUTE LOOP ''SELECT * FROM Table2 where > F1 = ''||r_Table1.aa||'' '' > r_TempTableHolder.aa := r_Table1.aa; > r_TempTableHolder.bb := r_Table2.bb; > return next r_TempTableHolder; > END LOOP; > END LOOP; > return; > end > ' > language 'plpgsql'; > > Of course, the one I am working is more complicated, but the concept is > pretty much the same. > All it does is actually query data from several tables instead of the > slow join query. > > However, the real problem for me is ......... Whenever, I wanna query > something different........ I have to drop and recreate all types and > functions. It is ok to modify the function cause there is a "CREATE OR > REPLACE FUNCTION" command. However, it is a trouble to drop and > re-create all type for me!! > > I am wondering How could a function to return the content of a temp > table that only exist during the execution of the function. > Any Help is welcome! > Thank You Very Much! > > Harry Yau > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
В списке pgsql-general по дате отправления: