Passing variables to psql script

Question

I've searched for this for a while but can't seem to find a definitive answer... I'm trying to run a psql script from the Terminal while passing a table variable name and another variable as part of the psql script.

BASH Command:

psql db1 -U user1 -f '/.../Desktop/.../sample.sql' -v table=pg2 -v v1="'2018-02-01'"; 

PSQL Script:

SELECT count(*) FROM :table WHERE datekey = :v1;

The above works. However, I'd like to be able to convert the tablename and the additional variable into a string in the script itself so I can use it in another function I've defined. For example, I'd like the tablename of pg2 to be available as a string 'pg2'. Similar requirement with the datekey variable. Is this possible? My observation is that passing variables is only possible if used in a CRUD operation or a WHERE clause.

Answer 1

From what I get from your question is a misunderstanding of what variables in bash do. Try if the script below helps:

#!/bin/bash

user="user1"
sqlfile='/.../Desktop/.../sample.sql'
table='pg2'
v1="'2018-02-01'"

psql db1 -U "$user" -f "$sqlfile" -v table=$table -v v1="$v1"

Answer 2

Off other answers on Stack / other sites, there's a fairly simple way to accommodate this within the psql script itself. For example, the original BASH command was:

psql db1 -U user1 -f '/.../Desktop/.../sample.sql' -v table=pg2 -v v1="'2018-02-01'"; 

...and within the script, there was a need to enter in two string parameters for a plpgsql function called "copyFunction". This was made possible by putting the table name in quotes after the colon and keeping the second variable as-is. If required, the second variable can also be put in quotes if needed.

SELECT copyfunction(:'table', :v1);