PostgreSQL - Select Into

In PostgreSQL, the select into statement to select data from the database and assign it to a variable.

Syntax:
select select_list
into variable_name
from table_expression;

In this syntax, one can place the variable after the into keyword. The select into statement will assign the data returned by the select clause to the variable. Besides selecting data from a table, one can use other clauses of the select statement such as join, group by, and having.

Example 1:

To demonstrate the use of select into statement we will use the sample database, ie, dvdrental.

do $$
declare
   actor_count integer; 
begin
   -- select the number of actors from the actor table
   select count(*)
   into actor_count
   from actor;

   -- show the number of actors
   raise notice 'The number of actors: %', actor_count;
end; $$;

Output:

In the above example we did the following:

• First, declare a variable called actor_count that stores the number of actors from the actor table.
• Second, use the select into statement to assign the number of actors to the actor_count.
• Finally, display a message that shows the value of the actor_count variable using the raise notice statement.

Example 2:

Here we will use the select into statement to assign the value of total number of films in the database to a variable film_count using the below statement:

do $$
declare
   film_count integer; 
begin
   -- select the number of films from the actor table
   select count(*)
   into film_count
   from film;

   -- show the number of films
   raise notice 'The number of films: %', film_count;
end; $$;

Output: