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.

Actually the latest PL/sh explicitly supports `psql` calls for database access: https://github.com/petere/plsh#database-access — Peter Eisentraut, Feb 13 '13 at 20:43

score 2 · Answer 1 · answered Feb 13 '13 at 16:37

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:

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

Chris Travers · Accepted Answer · 2016-06-16T15:15:54.047

1

I think your plsh script would work though you spelled psql wrong. 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.....

edited Jun 16 '16 at 15:15

answered Sep 11 '12 at 06:40

Chris Travers

25,424
6
65
182

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 06: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 07: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 03: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 05: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 06:58

theBuckWheat · Answer 3 · 2016-02-12T17:26:02.107

I found that the rpm'd version of Postgres that I had installed on Fedora did not have the perl or sh language extensions (like plperlu), so that option was out. However, the key to doing this securely is that the COPY function with the TO PROGRAM option will execute a command and pass stdin to it.

Create your bash shell script to run the intended external function. Make sure the first line in the script is of the format "#!/bin/sh" or "#!/bin/bash" (your preference here, this is the shell that will be used);

2) Change the shell script name to be a single word. IOW, no trailing ".sh" Set the permissions to: chmod u+x

3) Look at your path (echo $PATH), and pick a system directory to copy this script file to.

4) Your script will now be a "command" like any other. You can execute it with the COPY function with the TO PROGRAM(commandnamehere) function.

reference:

Turning a script into a command https://docs.fedoraproject.org/ro/Fedora_Draft_Documentation/0.1/html/RPM_Guide/ch14s04s04.html

score 0 · Answer 4 · answered Jul 11 '22 at 10:22

This answer is tested for Postgres 14.

In order to invoke a psql script within a stored procedure/function, Postgres copy command can be used.

copy copy_result_table from program 'psql  -U myuser -d mydb -f /tmp/scripts/create_db.sql';

For more details on the copy command, please refer to: postgres copy

score -2 · Answer 5 · answered Nov 26 '12 at 08:12

-2

You Can Use a command :

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

inside your Else block

answered Nov 26 '12 at 08:12

Satish Sharma

3,284
9
38
51

postgresql run external sql script from inside function

5 Answers5