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.
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; $$;
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.
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; $$;