0

Redshift support export data to CSV by executing this SQL query.

unload ('select * from bhuvi') 
to 's3://bhuvi-bucket/folder//user-data.csv' 
iam_role 'arn:aws:iam::1111111:role/myredshiftrole' 
delimiter '|'  GZIP";

I have different tables and need to export them to different locations. So I have a stored procedure like below. so I can pass the locations in a variable in the future.

CREATE OR REPLACE PROCEDURE test_variable()
 LANGUAGE plpgsql AS
$$
DECLARE
   sql text;
   s3_loc text;
   role text;
   deli text;
   query text;
BEGIN
   sql:='select * from bhuvi';
   s3_loc:='s3://bhuvi-bucket/folder/';
   role:='arn:aws:iam::1111111:role/myredshiftrole';
   deli:='|';
   query := "unload (sql) to 's3_loc/user-data.csv' iam_role 'role';
   execute query;
END
$$;

but somewhere the syntax is wrong. Im not able to find that. When I call this procedure, im getting this below error.

test=# call test_variable();
ERROR:  column "unload (sql) to 's3_loc/user-data.csv' iam_role 'role' " does not exist
CONTEXT:  SQL statement "SELECT  "unload (sql) to 's3_loc/user-data.csv' iam_role 'role' ""
PL/pgSQL function "test_variable" line 12 at assignment
test=#
  • It's no clear to me what are you trying to accomplish. Could you add the text of the desired final query? – McNets Aug 26 at 12:44
  • @McNets question updated with details. – Bhuvanesh Aug 26 at 12:48
1

You can't use double quotes for string literals. Do include a single quote in a string literal, it has to be escaped by doubling them:

query := 'unload (sql) to ''s3_loc/user-data.csv'' iam_role ''role''';

Additionally can't use names of variables from PL/pgSQL in a SQL string. You need to include the content of that variable in the query string you build:

query := 'unload ('||sql||') to '''||s3_loc||'/user-data.csv'' iam_role '''||role||'''';
  • Im getting this error. ERROR: syntax error at or near "sql" LINE 1: unload (sql) to 's3_loc/user-data.csv' iam_role 'role' if my question was not clear, just updated with the explanation. – Bhuvanesh Aug 26 at 12:48
  • @Bhuvanesh: see my edit – a_horse_with_no_name Aug 26 at 12:54
  • Awesome, thanks a lot, im getting some other error now. But this query:= is working great. I'll try to fix other errors which is related to aws. – Bhuvanesh Aug 26 at 12:56
  • I don't know if Redshift supports it, but usually using format() is easier when building dynamic SQL – a_horse_with_no_name Aug 26 at 12:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.