Thread: 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:

> 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
>