up vote 0 down vote favorite

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.

share|improve this question
83% accept rate
feedback

2 Answers

up vote 0 down vote accepted

I think your plsh script would work. It isn't accessing the database. It is calling another program to do that. As a big caveat, that would mean that script would be run in a separate transaction and even a separate session which may not be what you want.

If I was doing this I would create a table:

CREATE TABLE sql_jobs (
      job_id serial not null unique, -- machine key
      job_name text primary key,
      sql_to_execute text not null
);

Then you can select into a variable and execute. Of course be wary of security.....

share|improve this answer
I'm not sure I understand how an sql_jobs tables would help. The underlying problem, is not knowing what steps need to done in the initial session. Can you elaborate on the answer please? – TaninDirect Sep 11 '12 at 6:56
You would have to upload your SQL scripts into the sql_to_execute section. Then you could select into a variable and execute inside PL/PGSQL. The thing is that the only way you are going to be able to run a fully external SQL script is to do so with pl/sh in the way you mentioned but that has problems. The solution is to make sure it is no longer external by storing it in the db where it can be retrieved. – Chris Travers Sep 11 '12 at 7:20
Thanks @Chris Travers. I'm implementing this at the moment, and was wondering, beyond efficiency with repeated executions, are there any advantages to storing the script vs executing it directly from a variable? – TaninDirect Sep 12 '12 at 3:19
I can't think of any significant differences one way or another. You are going to have to plan and execute no matter how you do it. The only concerns are going to be transactional ones, and whether or not those are even issues will depend on whether this is the only thing you are doing in your transaction. – Chris Travers Sep 12 '12 at 5:30
I did think of one difference but not in your examples. CREATE DATABASE can't be done in a function but you are just creating tables etc which can be. – Chris Travers Sep 12 '12 at 6:58
feedback
up vote 0 down vote

You Can Use a command :

psql -d myDataBase -a -f /tmp/scripts/create_db.sql;

inside your Else block

share|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

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