store multiple rows with the SELECT INTO statement
<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div style="font-family: Verdana;font-size: 12.0px;">
<div>Hi,</div>
<div> </div>
<div>Why is it only possible to store one row by a query which returns multiple rows using the 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></body></html>
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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@web.de>
Show quoted text
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_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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>Thanks for the answers.</div>
<div><span style="font-family: Verdana, sans-serif, Arial, 'Trebuchet MS'; font-size: 13px; line-height: 1.6em;">I just can't understabd why it's not possible to store multiple columns returning from a</span><span style="font-family: Verdana, sans-serif, Arial, 'Trebuchet MS'; font-size: 13px; line-height: 1.6em;"> dynamic Select statement which is executet with EXECUTE into a temporary table.</span></div>
<div>If I'm gonna use the LOOP through the SELECT statement, how can insert the data from the record into the temp table?</div>
<div> </div>
<div>Janek Sendrowski</div></div></body></html>
Import Notes
Reply to msg id not found: 52250037.70001@gmail.com
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
| 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>The links don't work.</div>
<div>I don't know why.</div>
<div>how just don't know how to insert the data of a record in a table</div></div></body></html>
Import Notes
Reply to msg id not found: 52250037.70001@gmail.com
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Import Notes
Reply to msg id not found: trinity-28358485-cb48-4896-8786-773113ce5668-1378251248724@3capp-webde-bs40
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Import Notes
Reply to msg id not found: trinity-28358485-cb48-4896-8786-773113ce5668-1378251248724@3capp-webde-bs40