PL/PGSQL -- How To Return a Temp Table
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
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:
Show quoted text
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