Pivotal Knowledge Base

Follow

How to pass dynamic variable to psql

Goal

Provide a example to help you understand on how to pass variable to "psql" utility.

Solution

-- Single variable

Created a file "/tmp/file.sql" with the below sql

select * from :tbl;
select count(*) from :tbl;

Calling the script with variable passed

$ psql -f /tmp/file.sql -v tbl=a1

[gpadmin@mdw xx]$ psql -f /tmp/file.sql -v tbl=a1
 a
----
  4
  8
  3
  7
  1
  5
  9
  2
  6
 10
(10 rows)

 count
-------
    10
(1 row)

Similarly you can pass the variable on the where clause like for example

[gpadmin@mdw faisal]$ cat>/tmp/file.sql
select * from a1 where a=:con

[gpadmin@mdw faisal]$ psql -f /tmp/file.sql -v con=3
 a
---
 3
(1 row)

-- Multiple variables

The code in the file

[gpadmin@mdw xx]$ cat>/tmp/file.sql

\echo Rows from table :tbl
\echo

select * from :tbl;

\echo Total row count from table :tb
\echo

select count(*) from :tb;

Execution of the script

[gpadmin@mdw xx]$ psql -f /tmp/file.sql -v tbl=a1 -v tb=a2

Rows from table a1

 a
----
  4
  8
  1
  5
  9
  3
  7
  2
  6
 10
(10 rows)

Total row count from table a2

 count 
-------
    10
(1 row)

Comments

  • Avatar
    Vijendra Singh

    psql: could not connect to server: Connection refused
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
    [gpadmin@gpm ~]$ This error on each reboot of Greenplum master machine. please support to resolve permanently.

  • Avatar
    Faisal Ali

    Hi Vijendra,

    Not sure how this error is related to this article and why you have posted the error here and also we don't support any error message on the comment section of an article , if you have any issue please open a ticket if you have brought a support contract with pivotal.

    Also to add , if you shutdown the database ( since you mentioned you get the error during reboot ) and you are attempting to connect to the database that is a expected error message since there is no database instance to connect so not sure why are you concerned with the error , its not a bug ...

    Thanks
    Faisal

    Edited by Faisal Ali
Powered by Zendesk