PostgreSQL Tutorial: PL/pgSQL SELECT INTO Statement

September 17, 2023

Summary: in this tutorial, you will learn how to use the PL/pgSQL SELECT INTO statement to select data from the database and assign it to a variable.

Table of Contents

Introduction to PL/pgSQL SELECT INTO statement

The SELECT INTO statement allows you to select data from the database and assign the data to a variable.

The following illustrates the syntax of the SELECT INTO statement:

SELECT select_list
INTO variable_name
FROM table_expression;

In this syntax, you 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, you can use other clauses of the SELECT statement such as JOIN, GROUP BY and HAVING.

PL/pgSQL SELECT INTO statement example

See the following example:

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:

NOTICE:  The number of actors: 200

In this example:

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.