Re: store multiple rows with the SELECT INTO statement

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

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

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