I am trying to fetch resultset in DataGrip console from a Redshift stored procedure. Here is my stored procedure body
CREATE OR REPLACE PROCEDURE test_schema.get_redshift_table_ddl(
p_schema_name IN VARCHAR,
p_table_name IN VARCHAR,
rs_out INOUT refcursor
)
AS $$
DECLARE
v_query TEXT;
BEGIN
OPEN rs_out FOR SELECT * FROM test_schema.test_table LIMIT 5;
END;
$$
LANGUAGE plpgsql
;
I am calling the stored procedure in my console like this:
BEGIN;
CALL test_schema.get_redshift_table_ddl('test_schema','test_other_table','rs_out');
FETCH ALL FROM rs_out;
END;
Here is the AWS Reference that I am following: https://docs.aws.amazon.com/redshift/latest/dg/stored-procedure-result-set.html
Error
When I am calling the stored procedure, I am getting the below error
ERROR: function format(character varying, character varying, character varying) does not exist Hint: No function matches the given name and argument types. You may need to add explicit type casts. Where: SQL statement "SELECT FORMAT( $1 , $2 , $3 )"
Question
How can I pass the refcursor name while calling the stored procedure from console?
You should to use somewhere function format
, that is not supported on Redshift. Function format
is implemented in PostgreSQL 9.3. Amazon Redshift is based on some 8.x Postgres.