Is it possible to call a Postgres SQL script with '\i' but referencing the file as a string variable?

Question

I'm trying to invoke a SQL script within another SQL script using pgsql.

I already saw that I can use

\i 'path/to/file.sql'

where path/to/files.sql is between single quotes.

I was trying to replace 'path/to/file.sql' with a variable instead, like

DO $$
DECLARE
    ls_id INT := 271195;
    tokens VARCHAR(20);
BEGIN
    tokens := CONCAT(ls_id, '_tokens.sql');
    \i tokens
END $$;

Is this possible some way or another?

You cannot to use \i inside DO statement. \i is psql statement (client side). DO is SQL statement (server side). Both sides can be on different computers. — Pavel Stehule
– Pavel Stehule, Commented May 21, 2020 at 8:32

Richard Huxton · Accepted Answer · 2020-05-20 21:32:18Z

1

It's not something you can do directly in sql because \i is a psql command and not actually SQL at all.

But the following should demonstrate how you can go about it.

SELECT ('something' || '_and_' || 'something_else.sql') as filename \gset


\i :filename

The gset will create a psql variable for you based on the result of the query. Again the \gset is actually just for psql rather than being sent to the backend.

Then you can reference the psql variable prefixed by a colon.

Note that this is just a macro-style text substitution. You will need to deal with quoting spaces and backslashes and so on yourself.

answered May 20, 2020 at 21:32

Richard Huxton

23.6k5 gold badges43 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Caco Over a year ago

Your solution works outside the DO block, but It's important to call the file inside the DO block. As @Pavel Stehule said, it seems it's not possible to use \i as \gset inside a DOblock.

Richard Huxton Over a year ago

The SQL isn't executing on the client machine, so probably wouldn't be able to access the file anyway.

Collectives™ on Stack Overflow

Is it possible to call a Postgres SQL script with '\i' but referencing the file as a string variable?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related