PL/pgSQL: How to assign a table's column to a declared parameter?

Question

Look at this simple PL/pgSQL function:

create or replace function test() returns void()
as $$
declare 
a int;
begin
select more_than_one_value into a from my_table;
-- do some other job using a
end;
$$ 
language plpgsql;

When calling this function, only one row is selected because select into pass only one value to a, even though column more_than_one_value has many values.

Are there any convenient ways to select many values from a table and then assigned them to variable a ? I know create temp table as select is one method. I am looking for alternatives.

You could store them in an array. But if you want to insert those values into `another_table` anyway, just do`insert into another_table (col_1) select some_column from my_table` no need for a loop and **much** faster — , May 01 '16 at 07:18
Possible duplicate of [SQL Insert into ... values ( SELECT ... FROM ... )](http://stackoverflow.com/questions/25969/sql-insert-into-values-select-from) — krokodilko, May 01 '16 at 07:45
you can do `select a,b,c,d into a1,b1,c1,d1 from my_table limit 1;` define as many variables as you need. — Elad, May 01 '16 at 08:00
I assume by `column more_than_one_value has many values` you mean that the table `my_table` has many rows? BTW, `a` has not been declared in your example, neither as parameter nor as variable. — Erwin Brandstetter, May 02 '16 at 18:33

score 2 · Answer 1 · answered May 01 '16 at 09:04

2

There are a few options you can use:

You can use multiple variables in one INTO clause:

SELECT f1, f2, f3, f4 INTO v1, v2, v3, v4 FROM ...

You can use a record type:

DECLARE
 a my_table%rowtype
BEGIN
 SELECT * INTO a FROM my_table WHERE ...
 INSERT INTO another_table VALUES (a.*);
 ...

Or forget about variables altogether:

INSERT INTO another_table
 SELECT * FROM my_table WHERE ...

answered May 01 '16 at 09:04

Patrick

29,357
6
62
90

First , thank you very much. 1. Multiple variables is a good choice, but if there are 10000 rows in a table, then I have to define 10000 variables that all the values can be assigned. 2. even though a record type is used , only one record will be extracted. Well, I wish to extract more than row. 3. I konw this is the right way to perform data modification, but the question is not about insert. – k.xf May 01 '16 at 09:23
1

You would never use a variable per row. Instead, you get one row, process it and move on to the next row. You use an explicit `CURSOR` or loop over a query's result. That is the standard way of working in `plpgsql`. – Patrick May 01 '16 at 11:12

score 1 · Answer 2 · edited May 23 '17 at 12:31

You need a loop for that.

Multiple rows

If "many values" is supposed to mean multiple rows I suggest a FOR loop with an implicit cursor:

CREATE OR REPLACE FUNCTION test_rows()
  RETURNS void AS
$func$
DECLARE
   i int;  -- scalar variable
BEGIN
   FOR i IN  -- repeated assignment
      SELECT more_than_one_value FROM my_table
   LOOP
      -- do something with i
   END LOOP;
END
$func$  LANGUAGE plpgsql;

In many cases, there is superior set-based solution (plain SQL) to replace the loop.

The data type of column more_than_one_value must match the type of the variable (or parameter) or you get an error in the assignment.

i is a variable here, not a parameter. The latter term would refer to a variable passed to the function in the call.

Array

If "many values" is supposed to mean array, I suggest FOREACH:

CREATE OR REPLACE FUNCTION test_arr()
  RETURNS void AS
$func$
DECLARE
   i int[];   -- array variable
   elem int;  -- scalar variable
BEGIN
   SELECT INTO i more_than_one_value
   FROM my_table LIMIT 1; -- assign single array column

   FOREACH elem IN ARRAY i
   LOOP
      -- do something with elem
   END LOOP;

END
$func$  LANGUAGE plpgsql;

Postgres - array for loop

So that means there are no convenient ways to assign multiple values to a variable? Because I saw loop , a control structure, is used in your answer. — k.xf, May 03 '16 at 07:30
@k.xf: `multiple values to a variable` - what is that supposed to mean? A field, variable or parameter has *one* value. Are you talking about multiple rows or arrays or what is it you are talking about? — Erwin Brandstetter, May 03 '16 at 13:06

PL/pgSQL: How to assign a table's column to a declared parameter?

2 Answers2

Multiple rows

Array