Redshift Cursor

Question

I am trying to get maximum length of each column of a table in Redshift database with this query:

select MAX(LEN(Trim(col1)))mx_len from tbl1;

and insert result into a tmp table like:

tbl1 col1 50

For this, I am trying to write a cursor in redshift to fetch column name one by one and insert data into tmp table. I am getting column names from following query:

select columnname from information_schema.columns where table_name = 'tbl1'

but unable to write a cursor, can anyone help me with this. Thanks in advance.

Answer 1

You haven't describes how you are trying to declare a cursor so it is a little hard to know what the problem is. So I'll describe a generic path to making a cursor and fetching from it.

First a cursor only lives for the duration of the transaction so you must begin and keep open a transaction.

BEGIN;

Next you need to declare the cursor and what data it will contain.

DECLARE <cursor_name> CURSOR FOR SELECT X, Y, Z FROM ...

Then you need to fetch a row from the cursor.

FETCH FORWARD 1 FROM <cursor_name>; 

Then get the next row.

FETCH NEXT FROM <cursor_name>;

And on and on. When done close the cursor and end the transaction.

CLOSE <cursor_name>;
END;

Is this what you are doing? Is this not working?