0

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
1

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

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.

2
  • Thank you for the quick response! Would be nice if I was able to run multiple \set commands at once Sep 29 at 15:06
  • 1
    @MichaelPacheco: You can have multiple \set commands in the same line. Like: \set a b \set c d \set e f Sep 29 at 15:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.