Dynamic query to import from S3 bucket in pgAdmin

PostgreSQL
,
1

I want to write store procedure import from S3 bucket in pgsql. i am very new to pgsql.

Below Query is working standalone, i want to pass ‘company name’ dynamically using pgsql store procedure. how can i achieve this.

SELECT aws_s3.table_import_from_s3(‘temp_records’,
‘id,first_name,last_name’
,‘(FORMAT CSV, HEADER true)’,
aws_commons.create_s3_uri(‘snxyz.upload.s30.urlproject.com’, ‘company name/TempRecordTable.csv’, ‘us-east-2’)
);

I have tried using below but no luck. can any one help me.

CREATE OR REPLACE PROCEDURE import_test2(compname character varying)
LANGUAGE plpgsql
AS $BODY$
DECLARE
v_path varchar(500);
v_query varchar (65000);
v_sql varchar(65000);
BEGIN
v_path := compname||‘/’||‘TempRecordTable.csv’;
v_sql := ‘SELECT aws_s3.table_import_from_s3’;
v_query:= v_sql || (
‘’‘temp_records’‘’,
‘’‘id,first_name,last_name’‘’
,‘’‘(FORMAT CSV, HEADER true)’‘’,
‘aws_commons.create_s3_uri’||(‘’‘snxyz.upload.s30.urlproject.com’‘’, v_path, ‘’‘us-east-2’‘’));
execute v_query;
END;
$BODY$;

it gives error.

ERROR: column “‘id,first_name,last_name’” does not exist
LINE 1: SELECT aws_s3.table_import_from_s3(‘temp_records’,“'id,first…
^
QUERY: SELECT aws_s3.table_import_from_s3(‘temp_records’,”‘id,first_name,last_name’“,”‘(FORMAT CSV, HEADER true)’“,“aws_commons.create_s3_uri(‘snxyz.upload.s30.urlproject.com’,”“xyz Industries/TempRecordTable.csv””,‘us-east-2’)")
CONTEXT: PL/pgSQL function import_test2(character varying) line 14 at EXECUTE
SQL state: 42703

can anyone help to write this dynamic?

1 Like