How to pass parameters to sql script via psql \i command

Question

The psql \i command is able to execute a given SQL script but I need a way to pass parameters to the script.

Example: say you have this simple script

select * from :table LIMIT 1;

I've tried

my_db=> \i my-script.sql -v table="core.product"

but got this error

psql:my-script.sql:1: ERROR:  syntax error at or near ":"
LINE 1: select * from :table LIMIT 1;
                      ^
\i: extra argument "-v" ignored
\i: extra argument "table="core.product"" ignored

---

I know that running this on terminal will work, but I'm already inside psql.

psql -v table="core.product" -f my-script.sql

Answer 1

Use \set to set a variable inside psql.
Read about SQL interpolation in the manual here:

• https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-INTERPOLATION

Ideally, your script would read:

SELECT * FROM :"my_schema".:"my_table" LIMIT 1;

With schema and table double-quoted separately, for identifier-interpolation. Then set schema and table in psql without quotes like:

my_db=>\set my_schema core
my_db=>\set my_table product

Just the bare, case-sensitive names. Finally, execute:

my_db=>\i my-script.sql

This way, the script is safe against SQL injection.