3

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.

thomassantosh
  • 571
  • 1
  • 6
  • 21
  • See: [Difference between single and double quotes in bash](http://stackoverflow.com/q/6697753/3776858) – Cyrus Jun 24 '18 at 15:50

2 Answers2

4

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"
Ljm Dullaart
  • 4,273
  • 2
  • 14
  • 31
  • 1
    Thanks Ljm. I'm trying to find a way to take the $table and $v1 variable in the script and make them both into a string in the psql script itself. The reason I'm doing that is I have another plpgsql function in the psql script that requires the table name and the variable to be entered in as string parameters. Is there a way to pass the $table variable and in the psql script, convert it to a string? – thomassantosh Jun 24 '18 at 16:28
4

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);
thomassantosh
  • 571
  • 1
  • 6
  • 21
  • Great. This helped me for cases where the variable needs to be inserted in '....' like the following where simple :variable_name did not work: CREATE TABLESPACE :tablespace_name OWNER :db_owner LOCATION :'tablespace_folder'; – Horst Krause Jun 07 '19 at 12:44
  • Can you further elaborate on why the quotes are needed around table, but not v1? – NSjonas Dec 16 '20 at 21:41