CODEWITHSUNDEEP
postgresqlamazon-redshift
DJo
DJo

Reputation: 2177

store value in variable in Redshift

I am trying to store two variables in tw variables.

for example:

var1=$(select max(columnA) from table)
var2=$(select min(columnA) from table)

Can i combine the above statement and execute the same once and store max value in var1 and min value in var2 ?

I am using Redshift DB.

Upvotes: 1

Views: 11558

Answers (4)

Tharaka Deshan
Tharaka Deshan

Reputation: 1396

Sorry I am seeing this post in 2024.

So in 2024..SELECT INTO should work with your requirement.

Let's take an example. I have used current_date function here to get the current date, and date_part function to split it into segments.

Query: (Still no into here)

select
        date_part(year, current_date)::smallint as year,
        date_part(month, current_date)::smallint as month,
        date_part(day, current_date)::smallint as day;

Output:

year month day
2024 3 4

Now in a stored procedure, you can change this query to assign those values in to pre-declared variables:

SP:

select
        date_part(year, current_date)::smallint,
        date_part(month, current_date)::smallint,
        date_part(day, current_date)::smallint
into
        columnDate,
        columnYear,
        columnMonth;

Upvotes: 0

Joe Harris
Joe Harris

Reputation: 14035

Sadly AWS Redshift does not support variables or any kind of procedural features (PL/pgSQL) like triggers, stored procedures / functions, etc.

Edit: Redshift added PL/pgSQL stored procedure support in 2019. https://aws.amazon.com/blogs/big-data/bringing-your-stored-procedures-to-amazon-redshift/

Upvotes: 1

Javier Alba
Javier Alba

Reputation: 411

You can't define variables in Redshift.

But you could do the following:

WITH
  part1 as (select max(columnA) myMax from table),
  part2 as (select min(columnA) myMin from table)
SELECT part1.myMax, part2.myMin from part1, part2

Upvotes: 1

Jason
Jason

Reputation: 61

I know this question is old but I too needed to find a solution to the question. After much playing around here is the solution I came up with.

-- Create a temp table for storing the variables
create temp table tmp_vars (
  x int,
  y date
);

-- Store the values in the temp table
insert into tmp_vars (x, y) values
  ((select max(id) from table1), 
   (select max(date) from table2));  

-- Use the variable in a query
select *
from some_table
where date >= (select y from tmp_vars limit 1);

-- Drop the temp table
drop table tmp_vars;

Upvotes: 4

Related Questions