Execute SQL Script File using Greenplum psql Variable Substitution

  • Post author:
  • Post last modified:May 9, 2019
  • Post category:Greenplum
  • Reading time:5 mins read

Variable substitution is one of the important options that is widely used when you are executing any SQL scripts from the command line or from any shell scripts. Like any other databases such as Netezza, Redshift and Vertica, Greenplum also supports variable substitution using PostgreSQL or psql -v option. In this article, we will check how to execute a SQL script file using Greenplum psql variable substitution. We will also see some working example that uses Greenplum psql variable substitution method.

Execute SQL Script File using Greenplum psql Variable Substitution

The variable substitution is very important when you are calling the Greenplum SQL scripts from shell or any other scripting language. You can pass the values to a query that you are calling using various psql command line options.

The psql supports a variable substitution using -v option at a command line. You can set and use that variable within the script.

Psql Variable Substitution Options

The psql supports three options that you can use to pass variable to a SQL script on command line or shell script.

  • –v variable=value
  • –variable variable=value
  • –set variable=value

You can use any of the above-mentioned methods to substitute the variable value within Greenplum SQL scripts.

Related articles:

Below is the sample syntax that you can use to run SQL script file using psql variable substitution:

psql -U username -d database -h host -v name=val -f '/path/script.sql'

Above command is the sample example to substitute variable ‘name’ with ‘val’ value.

Greenplum psql Variable Substitution Example

Below are the working examples to run SQL script file using Greenplum psql variable substitution:

Using –v Variable Substitution method

This method is one of the widely used and easiest methods. Syntax and usage is same as other databases such as Netezza, Redshift etc.

[gpadmin@server-d861 ~]$ cat /home/gpadmin/sample_test.sql
select * from export_test
where id = :id_val;

[gpadmin@server-d861 ~]$ psql -U gpadmin -d template1 -h 192.168.200.100 -v id_val=100 -f '/home/gpadmin/sample_test.sql'
 id  | name  | city
-----+-------+------
 100 | black | LON
(1 row)

Use –variable Substitution method

Some of the database developer use more expressive variable substitution methods. 

[gpadmin@server-d861 ~]$ psql -U gpadmin -d template1 -h 192.168.200.100 --variable id_val=100 -f '/home/gpadmin/sample_test.sql'
 id  | name  | city
-----+-------+------
 100 | black | LON
(1 row)

Using –set Variable Substitution method

You can also use the variable substitution feature similar to Linux command line shells.

[gpadmin@server-d861 ~]$ psql -U gpadmin -d template1 -h 192.168.200.100 --set id_val=100 -f '/home/gpadmin/sample_test.sql'
 id  | name  | city
-----+-------+------
 100 | black | LON
(1 row)

You can use the any your favorite scripting program to dynamically pass the parameter to the Greenplum SQL script file.

Hope this helps 🙂

Tags: