In PostgreSQL, the PL/pgSQL SELECT INTO statement helps us store the table's data into a specific variable. The PL/pgSQL SELECT INTO statement fetches the data from a particular table and assigns it to the given variable. Different clauses can be used with the PL/pgSQL SELECT INTO statement for different purposes, such as the WHERE clause, GROUP BY clause, JOIN, etc.

This article explains how to use the PL/pgSQL SELECT INTO command to copy data from the selected table into a specific variable.

PL/pgSQL SELECT INTO Statement - Assign Data to a Variable

Follow the below instructions to assign data from a table to a variable:

- Specify the SELECT statement followed by the select expression.
- After that, utilize the INTO keyword followed by the variable name.
- Finally, specify the table’s name in the FROM clause.

Here, is the basic syntax for the PL/pgSQL SELECT INTO statement:

SELECT select_expression
INTO var_name
FROM tab_name;

Example 1: How to Use PL/pgSQL SELECT INTO Statement?

A sample table with the following records has been already created in the database:

SELECT *
FROM emp_bio;

Suppose we want to assign the total number of employees to a specific variable. For this, we will utilize the SELECT INTO statement, as follows:

DO $$
DECLARE emp_counter INTEGER;
BEGIN
SELECT COUNT(*)
INTO emp_counter 
FROM emp_bio;
RAISE NOTICE 'Total Employees: %', emp_counter;
END;
$$

In the above code:

- An integer variable named “emp_counter” is declared.
- The COUNT(*) is used to count the rows that match the specified criteria.
- The INTO keyword is used to specify the rows count into the “emp_counter” variable.
- The FROM clause keeps the name of the targeted table, i.e., “emp_bio”.
- The “RAISE NOTICE” is used to display the variable’s value.

The output shows that the total number of employees has been assigned to the emp_counter variable.

Example 2: How to Use PL/pgSQL SELECT INTO Statement With WHERE Clause?

In the following example, the WHERE clause is used with the SELECT INTO statement to copy the table’s data on specific criteria:

DO $$
DECLARE emp_count INTEGER;
BEGIN
SELECT COUNT(*)
INTO emp_count
FROM emp_bio
WHERE emp_sal > 40000;
RAISE NOTICE 'Total Employees Having Salary More than 40k: %', emp_count;
END;
$$

The clause will filter the employees based on their salary. Only those employees will be counted and assigned to the “emp_count” variable whose salary is more than 40,000”:

The output verified that only filtered data is assigned to the given variable.

Conclusion

PostgreSQL supports a “PL/pgSQL SELECT INTO” statement that assists us in storing the table's data into a specific variable. The stated command fetches the data from a particular table and assigns it to a specific variable. Different clauses can be used with the PL/pgSQL SELECT INTO statement for different purposes, such as the WHERE clause, GROUP BY clause, JOIN, etc. This post presented a detailed guide on how to assign a table's data to a variable using the SELECT INTO command in Postgres.