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.
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 theactor
table. - Second, use the
SELECT INTO
statement to assign the number of actors to theactor_count
. - Finally, display a message that shows the value of the
actor_count
variable using theRAISE NOTICE
statement.
Summary
- Use the
SELECT INTO
statement to select data from the database and assign it to a variable.