Error calling Redshift stored procedure with refcursor - Stack Overflow

admin管理员组

文章数量:1386925

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.

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.

• stored-procedures
• amazon-redshift

Share Improve this question Follow edited Mar 17 at 11:25 Lior Inerman asked Mar 17 at 8:15 Lior InermanLior Inerman 111 bronze badge 2

• What's in the table "sales"? This error is likely because no columns are returned from the select * from sales – 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 Commented Mar 17 at 15:45

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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;

本文标签： Error calling Redshift stored procedure with refcursorStack Overflow