I want to use variable values to a function and use it to a different function. However while setting the variables and appending it, i need advise. I have two options to choose with, however i am stuck with one or the other.
One - Creating a procedure and appending the values -- Code -
Create or replace procedure export_s3()
language plpgsql
as $$
declare s3_path varchar(100); path varchar(50); tble varchar(255); table_suffix varchar(20); region varchar(20); s3_url varchar(80);
BEGIN s3_path := 'talabat-prod-rds-billingdb-archive-eu-west-2'; path :='archive'; tble := 'sample_table_09_2022'; table_suffix := 'table'; region := 'eu-west-2';
SELECT aws_commons.create_s3_uri( s3_path, path/tble/table_suffix, region ) AS s3_uri_sample;
SELECT * FROM aws_s3.query_export_to_s3('SELECT * FROM tble ', :'s3_url', options :='format csv');
end; $$
--I get error in the last line where it is using colon. Also for the tble i want to append the month and year dynamically using this query/function -
select 'sample_table_'|| date_part('month', timestamp 'now()' ) ||'_'||date_part('year', timestamp 'now()');
Two -- I am trying to achieve the same from the psql cli but is stuck with the append part
Code -
Test=# \set path 'prod-archive-eu-west-2'
Test=# \set table 'test3/table'
Test=# \set region 'eu-west-2'
However i want to append the month and year to the table name using something like -
\set dat select date_part('month', timestamp 'now()' ) ||'_'||date_part('year', timestamp 'now()');
