CODEWITHSUNDEEP
postgresql
Vishal D
Vishal D

Reputation: 51

How to declare a variable to store column data

I need to use a variable in postgresql which can store column data. I am using this right now:

DECLARE 
   var_tmp numeric(1);
SELECT number FROM table_tmp;

Now here I have multiple data in the table_tmp.number but I am only getting last value of the column in var_tmp variable. what I want to achieve is to take all data from that column to my variable. for example if I have 10 rows in table_tmp.number then I want to assign all 10 rows to var_tmp variable. Is there a way to do that? Should I declare it some other way?

Upvotes: 0

Views: 475

Answers (2)

sandeep rawat
sandeep rawat

Reputation: 4957

There you have to use Temporary table.

  CREATE TEMP TABLE table_tmp(
   var_tmp numeric(1)
);

SELECT number INTO table_tmp FROM orig_table ;


 or


CREATE TEMP TABLE table_tmp AS
SELECT number
FROM orig_table;

Upvotes: 1

user330315
user330315

Reputation:

A variable can only hold a single value. If you need to store multiple values, you need an array:

DECLARE 
   var_tmp numeric[];
BEGIN
...
   SELECT array_agg(number) 
      into var_tmp 
   FROM table_tmp;

Upvotes: 2

Related Questions