How to pass a variable from shell script to sql file

Question

I am having a shell file named test.sh which is invoking other sql file 'table.sql'. 'table.sql' file will create some tables, but I want to create the tables in a particular schema 'bird'.

content of sql file.

create schema bird; --bird should not be hard coded it should be in variable
set search_path to 'bird';
create table bird.sparrow(id int, name varchar2(20));

content of shell file.

dbname=1ドル
cnport=2ドル
schemaname=3ドル
filename=4ドル
gsql -d ${dbname} -p ${cnport} -f ${filenam} #[how to give schema name here so that it can be used in table.sql without hardcoding]

I will execute my shell file like this

sh test.sh db1 9999 bird table.sql 

Answer 1

it is easier doing it in shell, eg:

dbname=1ドル
cnport=2ドル
schemaname=3ドル
filename=4ドル
gsql -d ${dbname} -p ${cnport} <<EOF
create schema 3ドル; --bird should not be hard coded it should be in variable
set search_path to '3ドル';
create table bird.sparrow(id int, name varchar2(20));
EOF

otherwise use psql variables