Re: PL/PGSQL -- How To Return a Temp Table - Mailing list 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
>