Thread: store multiple rows with the SELECT INTO statement
<div style="font-family: Verdana;font-size: 12.0px;"><div style="font-family: Verdana;font-size: 12.0px;"><div>Hi,</div><div> </div><div>Whyis it only possible to store one row by a query which returns multiple rows usingthe SELECT INTO statement.</div><div>and</div><div>How can I do a Query on a record varialbe, somehow like this:</div><div>SELECT* FROM v_rec</div><div> </div><div><span style="line-height: 1.6em;">Janek Sendrowski</span></div></div></div>
On 09/01/2013 05:23 PM, Janek Sendrowski wrote: > 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 You can't a record variable can only hold a single row. FYI SELECT INTO in plpgsql is not the same as the SQL SELECT INTO: http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW Tip: Note that this interpretation of SELECT with INTO is quite different from PostgreSQL's regular SELECT INTO command, wherein the INTO target is a newly created table. If you want to create a table from a SELECT result inside a PL/pgSQL function, use the syntax CREATE TABLE ... AS SELECT. One way I have gotten around this to create a temporary table in the function and fill it with data and then select from it as suggested above. > Janek Sendrowski -- Adrian Klaver adrian.klaver@gmail.com
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)
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@web.de>
Hi,Why is it only possible to store one row by a query which returns multiple rows using the SELECT INTO statement.andHow can I do a Query on a record varialbe, somehow like this:SELECT * FROM v_recJanek Sendrowski
Pavel Stehule <pavel.stehule@gmail.com> wrote: > PostgreSQL doesn't support a table variables Well, from a relational theory point of view, a variable which stores a relation is what a table *is*. PostgreSQL attempts to store data for temporary tables in RAM and spill them to disk only as needed. So IMO the response suggesting a temporary table was on target. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Thanks for the answers. I just can't understabd why it's not possible to store multiple columns returning from a dynamic Select statementwhich is executet with EXECUTE into a temporary table. If I'm gonna use the LOOP through the SELECT statement, how can insert the data from the record into the temp table? Janek Sendrowski
On 09/03/2013 12:10 PM, Janek Sendrowski wrote: > Thanks for the answers. > I just can't understabd why it's not possible to store multiple columns > returning from a dynamic Select statement which is executet with EXECUTE > into a temporary table. > If I'm gonna use the LOOP through the SELECT statement, how can insert > the data from the record into the temp table? I sent you an example off-list, does that not work? > Janek Sendrowski -- Adrian Klaver adrian.klaver@gmail.com
Janek Sendrowski <janek12@web.de> wrote: > I just can't understabd why it's not possible to store multiple > columns returning from a dynamic Select statement which is > executet with EXECUTE into a temporary table. You can: CREATE TEMPORARY TABLE AS SELECT ... http://www.postgresql.org/docs/current/interactive/sql-createtableas. html As the Notes section says: | This command is functionally similar to SELECT INTO, but it is | preferred since it is less likely to be confused with other uses | of the SELECT INTO syntax. Furthermore, CREATE TABLE AS offers a | superset of the functionality offered by SELECT INTO. Also see this: http://www.postgresql.org/docs/current/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT | Tip: Note that this interpretation of SELECT with INTO is quite | different from PostgreSQL's regular SELECT INTO command, wherein | the INTO target is a newly created table. If you want to create a | table from a SELECT result inside a PL/pgSQL function, use the | syntax CREATE TABLE ... AS SELECT. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
The links don't work. I don't know why. how just don't know how to insert the data of a record in a table
On 09/03/2013 02:52 PM, Janek Sendrowski wrote: > The links don't work. > I don't know why. > how just don't know how to insert the data of a record in a table The link I sent points to 39.6.4. Looping Through Query Results in the plpgsql documentation: http://www.postgresql.org/docs/9.2/interactive/plpgsql.html In the meantime, could you come up with some pseudo code that demonstrates what you want to do? -- Adrian Klaver adrian.klaver@gmail.com
On 09/03/2013 04:34 PM, Janek Sendrowski wrote:
> A loop through every input sentence
> FOR i IN 1..array_length(p_sentence, 1) LOOP
> FOR some_rec IN EXECUTE "SELECT * FROM table WHERE "Fulltextsearch
> statement" LOOP
> "Insert the current record data into the temp table"
> END LOOP;
> END LOOP;
> Do a next query on the table
I am CCing the list, other people will probably have other solutions to
offer.
To do what you show something like below. A shorter method would use
FOREACH for looping through the array, see;
39.6.5. Looping Through Arrays
CREATE TABLE source_table(id int, fld_1 varchar, fld_2 boolean);
INSERT INTO source_table VALUES (1, 'test', 't'), (2, 'test2', 'f'), (3,
'test3', 't');
CREATE OR REPLACE FUNCTION public.test_fnc()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
array_var integer[] := '{1, 2, 3}';
source_rec record;
target_rec record;
BEGIN
CREATE TEMP TABLE temp_tbl(id int, fld_1 varchar, fld_2 boolean);
FOR i IN 1..array_length(array_var, 1) LOOP
SELECT INTO source_rec * FROM source_table WHERE id = array_var[i];
INSERT INTO temp_tbl VALUES(source_rec.id, source_rec.fld_1,
source_rec.fld_2);
SELECT INTO target_rec * FROM temp_tbl WHERE id = array_var[i];
RAISE NOTICE 'Id is %, fld_1 is %, fld_2 is %', target_rec.id,
target_rec.fld_1, target_rec.fld_2;
END LOOP;
DROP TABLE temp_tbl;
RETURN;
END;
$function$
;
--
Adrian Klaver
adrian.klaver@gmail.com
On 09/03/2013 04:34 PM, Janek Sendrowski wrote: > A loop through every input sentence > FOR i IN 1..array_length(p_sentence, 1) LOOP > FOR some_rec IN EXECUTE "SELECT * FROM table WHERE "Fulltextsearch > statement" LOOP > "Insert the current record data into the temp table" > END LOOP; > END LOOP; > Do a next query on the table Forgot to mention the FOREACH loop is in Postgres 9.1+ -- Adrian Klaver adrian.klaver@gmail.com