set Date in parameters

[ekayabasi]

I am using pg_timetable to generate dump files for the database. While it is functioning correctly, I'm facing an issue with incorporating the current date into the generated file name. Here's the relevant portion of my code:

IF NOT EXISTS (SELECT FROM timetable.chain WHERE chain_name='CreateBackup') THEN PERFORM timetable.add_job('CreateBackup', '* * * * *', 'pg_dump','["--dbname=postgresql://{0}:{1}@{2}:{3}/{4}","--format=c","--file=Backups\\backup-%date%.sql"]'::jsonb,'PROGRAM'); END IF;

[pashagolub]

I'm not sure what {0}:{1}@{2}:{3}/{4} means... Anyway, you could use format() to achieve your goal, e.g.

Test=# select format('["--dbname=postgresql://{0}:{1}@{2}:{3}/{4}","--format=c","--file=Backups\\backup-%s.sql"]',current_date)::jsonb;
                                                format
------------------------------------------------------------------------------------------------------
 ["--dbname=postgresql://{0}:{1}@{2}:{3}/{4}", "--format=c", "--file=Backups\\backup-2023-08-18.sql"]
(1 row)

[ekayabasi]

{0}:{1}@{2}:{3}/{4} are placeholders that I'm filling with content. The code above is then executed using RelationalDatabaseFacadeExtensions.ExecuteSqlRaw. I'm attempting to incorporate format() into timetable.add_jobs, but it's not functioning as expected. How would you modify the code?

[pashagolub]

IF NOT EXISTS (SELECT FROM timetable.chain WHERE chain_name='CreateBackup') THEN 
    PERFORM timetable.add_job('CreateBackup', '* * * * *', 'pg_dump',format('["--dbname=postgresql://{0}:{1}@{2}:{3}/{4}","--format=c","--file=Backups\\backup-%s.sql"]',current_date)::jsonb,'PROGRAM'); 
END IF;

[ekayabasi]

Thank you! It is working now. I had to adjust the position of the formatting. Thanks for your quick response, and have a nice day!