I have an initializing sql script for PostgreSQL database. In the script, instead of hardcoding the name of the user, I wish to use the variable stored in my shell file. However I am not able to do it.
this is the shell file:
export
DB_HOST="xxx"
DB_PORT="xxx"
DB_USER="xxx"
DB_NAME="xxx"
psql -U $DB_USER -d $DB_NAME -v user1=$DB_USER -f initialize_postgres.sql
and i want to use it in the sql script which looks like the following:
GRANT
SELECT,
INSERT,
DELETE,
UPDATE ON ALL TABLES IN SCHEMA public TO :'user1';
GRANT USAGE,
SELECT ON ALL SEQUENCES IN SCHEMA public to :'user1';
CREATE FUNCTION save_property (IN prop varchar(50), IN val varchar(500)) RETURNS void AS $$
UPDATE properties SET "value"=val WHERE id=prop;
INSERT INTO properties (id, value)
SELECT prop, val
WHERE NOT EXISTS (SELECT 1 FROM properties WHERE id=prop);
$$ LANGUAGE sql;
GRANT EXECUTE ON FUNCTION public.save_property(varchar(50),varchar(500)) TO :'user1';
this is the error that is shown to me when I run the pipeline:

psql -U "$DB_USER" -d "$DB_NAME" -v "user1=$DB_USER" -f initialize_postgres.sqlHave a look at Store mysql result in a bash array variable, there are sample of variable use in both way. (Maybe you could be interested by myshell-connector.sh.)