Redshift SELECT INTO Variable – Example

Post author:Vithal S
Post last modified:December 13, 2021
Post category:Redshift
Reading time:4 mins read

The relational database that supports procedural language allows you to assign a value to local variable using SELECT statement within stored procedures. The databases such as Teradata, Oracle supports SELECT INTO clause to assign a value to a local variable. In this article, we will check how to assign a subquery value to a local variable using Redshift SELECT INTO clause within Stored Procedures.

Redshift SELECT INTO Variable

The SELECT INTO statement in Redshift retrieves data from one or more database tables, and assigns the selected values to variables. You can use SELECT INTO to assign a previously declared variable within a stored procedure or a RECORD type variable.

Redshift also selects rows defined by any query and inserts them into a new table. You can specify whether to create a temporary or a persistent table. This syntax is inline with Microsoft SQL Server T-SQL SELECT INTO syntax.

Redshift SELECT INTO Variable Examples

Following Redshift stored procedure uses the SELECT INTO statement to retrieve data from “TEST2” table and assign the selected values to variables.

CREATE OR REPLACE PROCEDURE record_type_example()
LANGUAGE plpgsql
AS $$
DECLARE
  rec_type RECORD;
  v_1 int;
BEGIN
  FOR rec_type IN SELECT col1 FROM public.test2
  LOOP
    select col1 into v_1
    from public.test2
    where col2 = rec_type.col1;
  END LOOP;
END;
$$;

Note that, you can use Redshift SELECT INTO clause to assign a variable only within a stored procedure. The statement is not supported outside stored procedures.

Redshift SELECT INTO New Table

Amazon Redshift SELECT INTO clause also selects rows returned by any query and inserts them into a new table. You can specify whether to create a temporary or a persistent table.

For example, following query sSelect all of the rows from the EVENT table and create a NEWEVENT permanent table.

select * into newevent from event;

and following example select the result of an aggregate query into a temporary table called PROFITS:

select username, lastname, sum(pricepaid-commission) as profit
into temp table profits
from sales, users
where sales.sellerid=users.userid
group by 1, 2
order by 3 desc;