Declare a variable in RedShift

Question

SQL Server has the ability to declare a variable, then call that variable in a query like so:

DECLARE @StartDate date;
SET @StartDate = '2015-01-01';

SELECT *
FROM Orders
WHERE OrderDate >= @StartDate;

Does this functionality work in Amazon's RedShift? From the documentation, it looks that DECLARE is used solely for cursors. SET looks to be the function I am looking for, but when I attempt to use that, I get an error.

set session StartDate = '2015-01-01';
 [Error Code: 500310, SQL State: 42704]  [Amazon](500310) Invalid operation: unrecognized configuration parameter "startdate";

Is it possible to do this in RedShift?

Answer 1

Slavik Meltser's answer is great. As a variation on this theme, you can also use a WITH construct:

WITH tmp_variables AS (
SELECT 
   '2015-01-01'::DATE AS StartDate, 
   'some string'      AS some_value,
   5556::BIGINT       AS some_id
)

SELECT *
FROM Orders
WHERE OrderDate >= (SELECT StartDate FROM tmp_variables);

Answer 2

Actually, you can simulate a variable using a temporarily table, create one, set data and you are good to go.

Something like this:

CREATE TEMP TABLE tmp_variables AS SELECT 
   '2015-01-01'::DATE AS StartDate, 
   'some string'      AS some_value,
   5556::BIGINT       AS some_id;

SELECT *
FROM Orders
WHERE OrderDate >= (SELECT StartDate FROM tmp_variables);

The temp table will be deleted after the transaction execution.
Temp tables are bound per session (connect), therefor cannot be shared across sessions.

Answer 3

No, Amazon Redshift does not have the concept of variables. Redshift presents itself as PostgreSQL, but is highly modified.

There was mention of User Defined Functions at the 2014 AWS re:Invent conference, which might meet some of your needs.

Update in 2016: Scalar User Defined Functions can perform computations but cannot act as stored variables.

Answer 4

Note that if you are using the psql client to query, psql variables can still be used as always with Redshift:

$ psql --host=my_cluster_name.clusterid.us-east-1.redshift.amazonaws.com \
     --dbname=your_db   --port=5432 --username=your_login -v dt_format=DD-MM-YYYY

# select current_date;     
    date    
------------
 2015-06-15
(1 row)

# select to_char(current_date,:'dt_format');
  to_char   
------------
 15-06-2015
(1 row)

# \set
AUTOCOMMIT = 'on'
...
dt_format = 'DD-MM-YYYY'
...
# \set dt_format 'MM/DD/YYYY'
# select to_char(current_date,:'dt_format');
  to_char   
------------
 06/15/2015
(1 row)

Answer 5

You can now use user defined functions (UDF's) to do what you want:

CREATE FUNCTION my_const()
    RETURNS CSTRING IMMUTABLE AS 
    $$ return 'my_string_constant' $$ language plpythonu;

Unfortunately, this does require certain access permissions on your redshift database.

Answer 6

Not an exact answer but in DBeaver, you can set up variables to use in your local queries in the IDE. Our team has found this helpful in testing before we put code into production.

From this answer: https://stackoverflow.com/a/58308439/220997

You should then be able to do:

@set date = '2019-10-09'

SELECT ${date}::DATE, ${date}::TIMESTAMP WITHOUT TIME ZONE

which produces:

| date       | timestamp           |
|------------|---------------------|
| 2019-10-09 | 2019-10-09 00:00:00 |

Again note: This only works in the DBeaver IDE. This SQL won't work when integrated in stored procedures or called from other tools

Answer 7

Redshift is built on old postgres. In running session:

set param.variable = 'xxx'
select current_setting('param.variable')
Output`xxx`

select * 
  from your_table
 where filter_column = current_setting('param.variable')