how can I create a stored procedure sql workbench that uses a redshift database?

Question

is it possible to create a stored procedures on sql workbench that uses a redshift database ?

I tried to put in some procedure found on the internet like this one

CREATE OR REPLACE FUNCTION proc_sample RETURN INTEGER
IS
  l_result INTEGER;
BEGIN
  SELECT max(col1) INTO l_result FROM sometable;
  RETURN l_result;
END;

but I get an error the cursor is not located inside a statement

help please.

Joe Harris Joe Harris · Accepted Answer · 2020-06-10T14:12:05

Here is my translation of your stored procedure for Redshift:

CREATE OR REPLACE PROCEDURE proc_sample (
      l_result  OUT  INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
  SELECT max(col1) INTO l_result FROM sometable;
END
$$;

You call this stored procedure in Redshift as follows:

BEGIN; CALL proc_sample(); END;
--  l_result
-- ----------
--        99

For more information see "Overview of stored procedures in Amazon Redshift"

how can I create a stored procedure sql workbench that uses a redshift database?

2 Answers