postgresql run external sql script from inside function

Question

What is the best way to run an external sql script from within a stored function in postgres?

this question explains how to call an external script from within a script being run in psql, however I need to wrap logic around the call, so it has to be done within a stored function.

EG.

/tmp/scripts$ cat create_db.sql 
CREATE TABLE dbVersion (
        versionNum VARCHAR(10) NOT NULL,
        applied TIMESTAMP
        PRIMARY KEY (versionNum)
);

/tmp/scripts$ cat upgrade_db.sql 
CREATE OR REPLACE FUNCTION UpgradeDB (dbName VARCHAR)
RETURN void AS $$
DECLARE
BEGIN
        IF EXISTS (SELECT datname from pg_database WHERE datname = dbName) THEN
                --Do upgrade code
        ELSE
                --Install Fresh
                \i /tmp/scripts/create_db.sql;
        END IF;
END;
$$ language plpgsql;

SELECT UpgradeDB('foo');

This (unsurprisingly) gives an error of

ERROR: syntax error at or near "\"

I could call out using plsh, something along the lines of (untested)...

CREATE FUNCTION callSQLScript(scriptPath text) 
RETURNS void AS $$
    #!/bin/sh
    plsql -f scriptPath
$$ LANGUAGE plsh;

SELECT callSQLScript('/tmp/scripts/create_db.sql');

but this seems very kludgy.

Just RTFM for plsh and it states 'The shell script can do anything you want, but you can't access the database' so this probably wont work.

NOTE, I wasn't able to copy/paste these code segments in, so there may be typos.

Answer 1

The reason \i does not work is because \i is a PSQL command NOT a Postgresql command therefore you CANNOT use \i inside a function as the function is evaluated by the Server.

Your Solutions:

1. write the Sql in the function
2. Write the sql in a table and use the EVAL function
3. Your suggested plsql (but you should use psql).