Re: store multiple rows with the SELECT INTO statement

Hello

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)

or in plpgsql

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

2013/9/2 Janek Sendrowski <janek12(at)web(dot)de>

> Hi,
>
> Why is it only possible to store one row by a query which returns multiple
> rows using the SELECT INTO statement.
> and
> How can I do a Query on a record varialbe, somehow like this:
> SELECT * FROM v_rec
>
> Janek Sendrowski
>