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
