Set search_path with file include in postgres psql command

user_779159 0
Viewed 497

How can I include multiple search paths in a psql command, so that multiple files can be run with different search_paths but all be run in one transaction?

psql
  --single-transaction
  --command="set search_path = 'a'; \i /sqlfile/a.sql; set search_path = 'b'; \i /sqlfile/b.sql;"

When I run this I get a syntax error at \i. I need to have the files included separately and they're generated dynamically so I'd rather run it using a --command than having to generate a file and using --file if possible.

psql postgresql sql
1 Answers

The manual about the --command option:

command must be either a command string that is completely parsable by the server (i.e., it contains no psql-specific features), or a single backslash command. Thus you cannot mix SQL and psql meta-commands within a -c option. To achieve that, you could use repeated -c options or pipe the string into psql [...]

Bold emphasis mine.

Try:

psql --single-transaction -c 'set search_path = a' -c '\i /sqlfile/a.sql' -c 'set search_path = b' -c '\i /sqlfile/b.sql'

Or use a here-document:

psql --single-transaction <<EOF
set search_path = a;
\i /sqlfile/a.sql
set search_path = b;
\i /sqlfile/b.sql
EOF

The search_path needs no quotes, btw.

user_9398600
Related