Re: PL/PGSQL -- How To Return a Temp Table

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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>