Re: store multiple rows with the SELECT INTO statement

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(dot)klaver(at)gmail(dot)com