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.klaver@gmail.com