Search code examples
sqlpostgresqldbt

DBT SQL + Postgres set session variable using current date

I am trying to set a variable I later use within a query to send some data as a csv to S3. I'd like to use the current timestamp as part of my filename, in DBT I have access to SQL, but can't quite crack how to make this happen.

My variable setting code, I know this doesn't look any good or work, this is what I'd like to achieve:

SET session data_output.filename = GETDATE()+"-attrs.csv";

Where I use this variable:

aws_commons.create_s3_uri(
    'table_name',
    current_setting('data_output.filename')::varchar,
    'us-east-1'
)

Expected output '2021-11-10-170423-attrs.csv' for my data_output.filename

Solution

  • Use set_config:

    SELECT set_config(
          'data_output.filename',
          current_date || '-attrs.csv',
          FALSE
       );