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=#
Best Answer
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:
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: