Creating stored procedures in SQL Workbench for Redshift databases

Data and Databases
1

I’m trying to set up a stored procedure in SQL Workbench that works with a Redshift database. Is this even possible? I’ve been searching online and found some examples, but when I try to use them, I keep running into problems.

Here’s a sample procedure I attempted:

CREATE FUNCTION get_max_value() RETURNS INTEGER AS $$
DECLARE
  max_val INTEGER;
BEGIN
  SELECT MAX(column_a) INTO max_val FROM example_table;
  RETURN max_val;
END;
$$ LANGUAGE plpgsql;

When I run this, I get an error saying ‘the cursor is not located inside a statement’. I’m not sure what I’m doing wrong here. Does anyone have experience with this or know how to make it work? Any help would be greatly appreciated!

2

hey lucaspixel23! i’ve been curious about redshift stored procedures too. have you tried using the CREATE PROCEDURE syntax instead of CREATE FUNCTION? redshift has some unique quirks. maybe we could explore this together? what specific task are you trying to accomplish with your procedure?

3

yo lucaspixel23, i feel ya. redshift can be tricky. have u tried the newer CREATE PROCEDURE syntax? it’s diff from regular SQL. also, make sure ur using the right language - try SQL instead of plpgsql. lemme kno if u need more help!

4

I’ve encountered similar challenges with Redshift stored procedures. The key is to use the Redshift-specific syntax, which differs from standard PostgreSQL. Here’s a modified version of your example that should work:

CREATE PROCEDURE get_max_value()
AS $$
DECLARE
max_val INTEGER;
BEGIN
SELECT MAX(column_a) INTO max_val FROM example_table;
RAISE INFO ‘Max value: %’, max_val;
END;
$$ LANGUAGE plpgsql;

Note the use of CREATE PROCEDURE instead of CREATE FUNCTION, and the RAISE INFO statement to output the result. Remember to execute the procedure using CALL get_max_value(); after creation. This approach aligns with Redshift’s stored procedure implementation and should resolve your issue.