Making the postgreSQL script configurable

Question

I have created a sql script to delete a table from a database in which the records is older than 7 days with below schema.

delete.sql

DELETE from abc where eventtime::date <=(CURRENT_TIMESTAMP-INTERVAL '7 DAYS')::date;
COMMIT;

I am executing the above sql script using my shell script as below:

xyz.sh

sudo  -u user psql --d db --file=<<path>>/delete.sql

Now, i have to make this script configurable i.e. user can change the duration which can be read by script directly rather than changing the script always. So, i am trying to create some .config file and to use it as a reference in sql script. But i am unable to do it. Any help with how i can pass the variable from .config file to postgreSQL script file.

Expected scenario:

.config file

# duration to delete the records from the database
DURATION =7 DAY

delete.sql

DELETE from abc where eventtime::date <=(CURRENT_TIMESTAMP-INTERVAL '$DURATION')::date;
COMMIT;

Thanks in Advance.

I really don't understand this use case. How is the user going to execute this script? What OS? If you're using psql, you can use a bash script or bat file to prompt user for duration and then run your query. — bfris
– bfris, Commented May 26, 2018 at 5:32

teppic · Accepted Answer · 2018-08-14 19:21:02Z

3

Edit: postgres has built in support for parameters in scripts (the -v flag). See the answer from op below.

In xyz.sh:

#!/usr/bin/env bash

. ./.config

sudo -u user psql -d db -c "DELETE FROM abc WHERE eventtime::DATE <=(CURRENT_TIMESTAMP - INTERVAL '${DURATION} days')::DATE"

You'd have to quote the duration in your .config file because there's a space in it:

# Interval in the past to keep
DURATION="7 DAY"

If you want to keep the script in a separate file you'll have to do token replacement on it before feeding it to psql. A utility like sed can be used:

In xyz.sh:

#!/usr/bin/env bash

. ./.config

cat delete.sql | sed -e "s/DURATION/${DURATION}/g" | sudo -u user psql -d db

edited Aug 14, 2018 at 19:21

answered May 26, 2018 at 5:33

teppic

7,3161 gold badge34 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Lucifer Over a year ago

I have updated the query. I have added my shell script from where my sql script is being called.

teppic Over a year ago

Is there an objection to including the SQL in the bash script? If you include it inline you will be able to template it as above. It will be more work to read it from a separate file.

Lucifer Over a year ago

yes i was able to achieve from the shell script giving it inline but i was looking for someway to configure sql script.

teppic Over a year ago

You could use sed to do token replacement. I've updated the example.

teppic Over a year ago

sed is being used here to replace the word DURATION with the value from the config file. The actual sed command being executed is s/DURATION/7 DAY/g - bash does the variable substitution in the command before executing it. In the land of sed s means substitute and g means global (every occurrence).

|

Lucifer · Accepted Answer · 2018-05-31 03:49:23Z

1

I was able to try one more way which does not involve sed command

.config file

# Interval in the past to keep
DURATION='7 DAYS'

xyz.sh

#!/bin/bash

source /<<path>>/file.config

sudo -u user psql -d db -f delete.sql -v DURATION="'${DURATION}'"

"'${DURATION}'" returns the output as '7 DAYS'

delete.sql

DELETE from abc where eventtime::date <=(CURRENT_TIMESTAMP-INTERVAL :DURATION)::date;
COMMIT;

So, now when you execute the xyz.sh file it will met the expected scenario.

answered May 31, 2018 at 3:49

Lucifer

18914 bronze badges

Collectives™ on Stack Overflow

Making the postgreSQL script configurable

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related