Error calling Redshift stored procedure with refcursor

Question

I’m following this AWS Redsfhit documentation to create a stored procedure that returns a result set using a cursor, but I keep getting the following error:

Number of cells in the row (0) does not match number of headers (1) [ErrorId: 1-67d7d631-44e721da51cfca6a75a2bd27]

Here’s the stored procedure I created:

CREATE OR REPLACE PROCEDURE get_result_set (rs_out INOUT refcursor) 
AS $$ 
BEGIN 
  OPEN rs_out FOR 
  SELECT * FROM sales; 
END; 
$$ LANGUAGE plpgsql;

And here’s how I’m invoking it:

BEGIN; 
CALL get_result_set('mycursor'); 
FETCH ALL FROM mycursor; 
COMMIT;

The error occurs after executing the CALL get_result_set('mycursor'); command.

I’ve verified that the sales table contains data, but I’m unsure what I’m doing wrong, as this is a very simple procedure based on the documentation.

What's in the table "sales"? This error is likely because no columns are returned from the select * from sales — Bill Weiner
– Bill Weiner, Commented Mar 17 at 15:45
I verified table sales contains data, and I'm selecting it all from the stored procedure.. — Lior Inerman
– Lior Inerman, Commented Mar 17 at 15:45

Bill Weiner · Accepted Answer · 2025-03-18 16:26:33Z

It took a bit to see why you were see what you were seeing. The below code (I commented out the first line so as to not cause a conflict with your table "sales") works just fine on my bench.

It looks like you are running on the AWS console's Query Editor V2, yes? If I run this code there it gives the error you are seeing but only if I run it against a provisioned cluster. If I run it against a serverless instance it goes one statement further and fails on the FETCH.

So I believe you have found a bug in the Query Editor. Likely that it isn't treating transactions correctly but I'm can't be sure. I think you can make progress if you move to a proper workbench and you will want to submit a bug to AWS to get this fixed.

-- create table sales as select 'this is dummy data'::varchar(100) as A ; 

CREATE OR REPLACE PROCEDURE get_result_set (rs_out INOUT refcursor) 
AS $$ 
BEGIN 
  OPEN rs_out FOR 
  SELECT * FROM sales; 
END; 
$$ LANGUAGE plpgsql;

BEGIN; 
CALL get_result_set('mycursor'); 
FETCH 1000 FROM mycursor; 
COMMIT;

Collectives™ on Stack Overflow

Error calling Redshift stored procedure with refcursor

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related