1

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?

Caco
  • 1,601
  • 1
  • 26
  • 53
  • 2
    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 May 21 '20 at 08:32

1 Answers1

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.

Richard Huxton
  • 21,516
  • 3
  • 39
  • 51
  • 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 `DO`block. – Caco May 21 '20 at 10:57
  • 1
    The SQL isn't executing on the client machine, so probably wouldn't be able to access the file anyway. – Richard Huxton May 21 '20 at 13:26