Thread: Alternative to temp tables?

Hi,

I have a simple function that returns a set of rows:

CREATE OR REPLACE FUNCTION foo()
  RETURNS SETOF record AS
$$
BEGIN
    RETURN QUERY SELECT * FROM people WHERE last_name = 'jones';
END
$$
LANGUAGE 'plpgsql'

In a separate function, I call the function and store the results in a
temp table using this syntax:

INSERT INTO tmp_tbl SELECT * FROM foo()


This works, but I'd like to know if there's another way to hold the
results.  Can I get the results from foo() and store those in a local
var such as

recs record[]   OR
recs people[]

Or are temp tables the only way to hold table-based results?

On Sat, Sep 12, 2009 at 12:51 AM, Postgres User
<postgres.developer@gmail.com> wrote:
> Hi,
>
> I have a simple function that returns a set of rows:
>
> CREATE OR REPLACE FUNCTION foo()
>  RETURNS SETOF record AS
> $$
> BEGIN
>    RETURN QUERY SELECT * FROM people WHERE last_name = 'jones';
> END
> $$
> LANGUAGE 'plpgsql'
>
> In a separate function, I call the function and store the results in a
> temp table using this syntax:
>
> INSERT INTO tmp_tbl SELECT * FROM foo()
>
>
> This works, but I'd like to know if there's another way to hold the
> results.  Can I get the results from foo() and store those in a local
> var such as
>
> recs record[]   OR
> recs people[]
>
> Or are temp tables the only way to hold table-based results?

Nope, starting with pg 8.4 you can create temp result sets with the
WITH keyword.