Re: store multiple rows with the SELECT INTO statement - Mailing list pgsql-general

PostgreSQL doesn't support a table variables, but you can use a arrays.

postgres=# create table foo2(a int, b int);
CREATE TABLE
postgres=# insert into foo2 select i, i+1 from generate_series(1,4) g(i);
INSERT 0 4
postgres=# select * from foo2;
 a | b
---+---
 1 | 2
 2 | 3
 3 | 4
 4 | 5
(4 rows)

postgres=# select array(select row(a,b) from foo2);
             ?column?             
-----------------------------------
 {"(1,2)","(2,3)","(3,4)","(4,5)"}
(1 row)
                                                             ^
postgres=# select * from unnest(array(select row(a,b) from foo2)) as (a int, b int);
 a | b
---+---
 1 | 2
 2 | 3
 3 | 4
 4 | 5
(4 rows)

postgres=# do $$
declare
  a foo2[] = array(select row(a,b) from foo2);
  r record;
begin
  for r in select * from unnest(a)
  loop
    raise notice '% %', r.a, r.b;
  end loop;
end;
$$;
NOTICE:  1 2
NOTICE:  2 3
NOTICE:  3 4
NOTICE:  4 5
DO

Regards

Pavel